This strategy is the most common strategy. It is generally based on backup types that have been available since the introduction of SQL Server, yet if the environment is right it might also utilize one of the newer backup types to improve recovery time. This strategy is easier to administer than the file-based backup strategy and offers up-to-the-minute recovery and point-in-time recovery. You can change between the Full and Bulk-Logged recovery models without breaking the continuity of the log. Remember to also review the recommended practices for batch processing, because using the Bulk-Logged recovery model will significantly reduce the work-loss exposure created during the changes in recovery models.

The typical strategy consists of automated backups of these types:

Their frequency is dictated by your database but usually follows something like this:

Consider the following example using full and transaction log backups, as well as differentials, because performing full database backups weekly leads to a large number of logs being created hourly. In a week, if you performed a transaction log backup every hour, you would have nearly 170 transaction log backups (24 × 7 = 168, but because you perform one full backup, no transaction logs are generated during the process).

Restoring all of these transaction logs from transaction log backups takes time and is cumbersome. You want a streamlined process, not one in which you need to write close to 180 Transact-SQL statements. So although it takes significantly less time than the original modification took to apply all of the operations in the transaction logs, it could still take a lot of time to implement (again, no computations, no functions, just data is applied—but for every log row). Instead of having to build a version of a database by reapplying changes row by row, you can get to a specific point in time much more quickly by using a differential backup.

In this scenario full database backups are represented as F₁ and F₂, differential database backups are represented as D₁, D₂, D₃, and D₄, and 20 transaction log backups are shown as l₁ through l₂₀. At point in time x you decide to recover the database.

This example shows a total of 27 backups. With this backup strategy, you have multiple potential recovery paths and more efficient recovery. The most optimal recovery path is to use the last full database backup, the last differential backup after that full database backup, and then all of the transaction log backups after the differential backup to restore the database up-to-the-minute when it is possible.

This strategy is optimal because you can recover all of the activity that occurred between the full database backup at F₂ and the differential backup at D₂ with only a single restore. This restore is a copy of how the pages looked at the point in time when the differential backup was made, and it includes all changes since the full backup. Remember, each differential database backup includes all changes since the last full backup. Each differential could get larger and larger, so you will still want to periodically perform a full database backup. However, differentials offer the ability to perform a potentially smaller backup (maybe a fraction of the entire database) more frequently and not burden the system by performing full database backups. But what if the last differential backup was bad? Do you have any other options? You could use the differential backup prior to that as shown here:

If that differential backup were bad as well, then you could use only the transaction logs and still recover:

If the last full database backup were bad, you have other options. If the backup at F₂ is bad, go back to F₁. However, when you go back to a previous full database backup, you must remember that the only differentials you can apply are those that apply to that version of the full database backup. For the full database backup performed at F₁ you can apply any differentials performed after that full backup (not another) and before the next full database backup (F₂ in this case). If F₁ were required for the restore then the most optimal restore sequence starting with F1 would be the following:

You could then repeat the same process described previously and return to an earlier differential if the differential database backup D₃ were bad. A complete list of all of your remaining options follows:

In fact, even if this full database backup at F1 were bad, you could return to the previous full database backup, assuming you kept it, and still roll forward to the last transaction log backup. Or you could return to the previous full database backup—or the one before that, or the one before that—as long as you have the entire sequence of log backups to apply.

As part of an overall cost savings analysis, a company determines that by implementing a differential backup strategy its backup needs can be covered completely and the company can save money at the same time by implementing a differential backup strategy. Because transaction log backups are critical, the company decides that transaction log backups should be performed to disk. Additionally, it augments the backup strategy with a log shipping site at a remote location. The current full database backup strategy costs $44,380 annually. The breakdown of the costs associated with it is as follows:

However, the database is over 1 terabyte (TB) and only 2 percent of the data changes daily. If 1 TB of data can fit on 3 tapes, several database differential backups can easily fit on the same tapes. In fact, numerous differentials can even fit on a single tape. The first day the backup will be 20 GB (the total backup space needed 20 GB); the second day it will be 40 GB (if appended, the total backup space needed is 60 GB); the third day it will be 60 GB (again, if appended, the total backup space needed is 120 GB); the fourth day it will be 80 GB (if appended, the total backup space needed is 200 GB); and so on.

Effectively, you could fit an entire week’s worth of differential database backups on one set of tapes; however, the decision is made to rotate tapes after three differential database backups and store them on only a single tape (instead of using a stripe set). This strategy yields a significant savings in both tape media and operational costs for a total of $10,350 annually. The breakdown of the costs associated with it is as follows:

The total saving for using differential backups is $34,030 ($44,380 – $10,350). The differential database backup saves 75 percent of the cost of the full database strategy. Additionally, because the differential database backs up only a maximum of 12 percent of the database (2 percent per day over the six days until a full database backup), the type of backup affects the production workload significantly less than the full database backup. Furthermore, the transaction logs are paused for a far shorter period of time, keeping the log shipping secondary more up to date.

This strategy was introduced and possible in Microsoft SQL Server 7.0, but there were limitations using SQL Server 7.0 that made it less robust than the implementation in SQL Server 2000. The most significant difference is that in SQL Server 7.0, files could not be backed up individually if they were part of a filegroup or if there were data dependencies within other filegroups. If they were, SQL Server required that all files or filegroups be backed up at the same time. In SQL Server 2000, you can back up any file or filegroup at any time, with the only restriction being that your database be in the Full or Bulk-Logged recovery model (which all production databases should be running in anyway).

The typical strategy consists of automated backups of these types:

In the file-based backup strategy, you can completely customize the frequency and granularity of the backups—a huge benefit for VLDB—in which different portions of the data have different uses. You should customize the granularity based on the way the data is distributed within the database (such as targeting specific types of data to specific locations within a database). Moreover, you should determine the frequency of each backup based on the type of data that that is stored there.

Determining which type of user data should go into your filegroups requires some strategizing. As a simple start, the Primary filegroup should contain only the system tables, and you need only one transaction log file. As discussed earlier, you need only one transaction log file because frequent log backups minimize the space required to hold the changes, and you do not gain performance when you have more than one transaction log. For example, if your transaction log is backed up every minute, the size of the transaction log is the size to hold one minute’s worth of log entries.

You should store user-defined data in nonprimary filegroups. In general, you should rarely split individual tables and their indexes into separate filegroups. However, you might consider this approach if you have a single large table that you want to place directly into a filegroup for better scan performance and more backup options. Remember, do not implement it without testing properly.

Here are a few recommendations for filegroup usage:

In fact, the file-based backup strategy provides some key benefits because it is so flexible, yet it is also complex in design, administration, and recovery. The most important part is making sure you back up every file at some point. You must achieve a complete backup set similar to what the full database backup provides (all data files must have been backed up) so that you can recreate the database structure completely, if necessary (for example, if you need to recover the database from the ground up). You can create this backup set (from which you can build the database framework, if needed) by either backing up the files individually, backing up the filegroups individually, or backing up some combination of the two—as long as all files are backed up at some point.

Both strategies use differential backups as well as transaction log backups, which provides some significant benefits, such as the following:

Although this strategy is the most common, you must be aware of its pros and cons. Better to know now your potential for downtime, data loss, or both during planning rather than at the time of a disaster!

The pros include the following:

Although this strategy is not widely used, you should consider it if you have a VLDB. This strategy requires design techniques that focus on physical placement of objects to best use the strategy. However, any database can use some of the basic principles of this strategy by having multiple files. This strategy is best for situations in which log shipping is used and recovery is being optimized for hardware failure. If human error is the main purpose for choosing this strategy, you must be aware of the restrictions!

The pros include the following:

In addition to creating permanent or temporary devices, there is an output device called NUL. NUL is an operating system "device" that acknowledges a write operation without actually writing the data anywhere.

NUL is used in special cases to test the read side of the backup process; however, the backup is not saved. Instead the backup is processed as if it were a regular backup but the write portion of the backup is never actually done. There are a few interesting uses for this device:

Using the NUL device in code is performed the same way as the use of any device physically defined at backup. The following full database backup command uses the NUL device to test the size and impact of the backup:

BACKUP DATABASE Inventory TO DISK = 'NUL'

go

The result will be similar to the following:

Processed 17888 pages for database 'Inventory', file 'InventoryData' on file 1.

Processed 1 pages for database 'Inventory', file 'InventoryLog' on file 1.

BACKUP DATABASE successfully processed 17889 pages in 5.872 seconds (24.955 MB/
sec).

From this output, you can determine the size of your backup. SQL Server does not need to back up every page of the database; instead, it needs to back up only allocated extents. The size of a backup is therefore the size of the database minus the free space. How large is that? In the previous example, a full database backup was performed to the NUL device. The backup showed that a total of 17,889 pages were processed. At 8 KB per page, the backup size would be about 146.5 MB. This information is helpful to know if you need to estimate storage because of space restrictions or if you are backing up to disk to copy the backup device(s) to CD or DVD.

As an alternative, you could use the sp_spaceused system procedure or run a system table query to get the same number that sp_spaceused produces. However, of the two, using a system table query is easier to add to programmatic scripts, batches, and so on. To get an estimate of the space usage for a database, use the following query:

SELECT sum(reserved)*8/1024 -- Estimate in MB

FROM sysindexes

WHERE indid IN (0, 1, 255)

Unfortunately, both sp_spaceused and this system table query might not be as accurate as you need them to be sometimes. To get a more accurate value you can run sp_spaceused @updateusage = 'true' and the information will be more accurately gathered by scanning the entire database; however, this can create quite a burden on the system.

Finally, there are some technical notes to be aware of when using the NUL device. Performing a backup to NUL will indicate that the backup strategy has begun—even though nothing exists from which you could recover. This situation requires you to manage the transaction log if you are not already doing so. You must always perform another real full database backup after backing up to the NUL device because you will need a proper full database backup from which you could recover. Differential database backups performed after a backup to the NUL device cannot be applied to a previous full database backup; in fact, only the transaction log backups are useful. Performing any full database backup—even with the NUL device—resets the differential bitmap. Because this backup to NUL performs exactly the same steps as a regular full database backup without the actual capture, you should use this option only in test or development scenarios when you are truly looking to evaluate the performance impact of the read side of backup on your disk subsystem.

To improve performance, you can use multiple backup devices in parallel to create a striped backup set. The biggest benefit of creating a striped set is that you can back up to as many as 64 independent devices at a time. If you have two drives to which you would like to store backups, then you might be able to cut the backup time in half (depending on system configuration). Realize, however, that the benefits of parallel striped backup are really only achieved when the devices are different physical devices. Backing up to multiple backup devices on the same physical disk is probably not beneficial at all unless you need the files to be of a certain size. For example, you might back up to three files on one physical disk to ensure that the three files are small enough to individually burn to a certain media type, such as a 680-MB CD-R. You can then later restore directly from the CDs. If you had backed up to one large file and then split it across multiple CDs, you would need to put the file back together on disk before commencing with the restore process.

Additionally, if you have multiple devices that you would like to act as one, consider creating a RAID 0 array and then using that as your backup location. Creating a parallel striped backup has the same configuration as a RAID 0 array. There is zero redundancy, and only performance gains because to successfully restore from a parallel striped backup, you must have all devices at the time of the restore. This is not true of tape devices, but it also means that you need all of the tapes to perform your restore; each media family is processed before another one can be used. Handling media families that span multiple tapes is not an easy task.

When you back up to multiple devices in parallel to a hard drive, for example three devices, approximately one-third of the database will be placed on each of the three devices. For tape, an algorithm is used where a faster tape can consume more of the data. If one device or file is not accessible at the time of recovery, the two remaining devices are essentially useless.

The following example shows a full database backup of the Inventory database to three devices: InventoryBackup1, InventoryBackup2, and InventoryBackup3.

BACKUP DATABASE [Inventory]

TO [InventoryBackup1], [InventoryBackup2], [InventoryBackup3]

If the file or tape on which InventoryBackup2 resides were to become damaged, then the complete backup would be unusable. You cannot recover any data from the two remaining devices (InventoryBackup1 and InventoryBackup3) without also having the data from InventoryBackup2.

When creating a parallel striped backup, you must have good naming conventions. If recovery were necessary, you would want to be able to find all components of your backups as quickly as possible. In fact, in addition to naming the individual backup devices, you can name the media set. Creating a media set name makes finding backups a lot easier because each device shows the media set name and description. Without the name and description, the only identifier you will have is a globally unique identifier (GUID) that SQL Server places into the backup device’s header at the time of media set creation (which is a good secondary check, but it is harder to read and harder to work with). Therefore, you should always use a media set name and description, and you must set them with the very first backup. When performing the first backup (or later if you want to break up a media set or change the use of a device), you can add the FORMAT option to define the device’s use.

Using the same example as before, the syntax changes to the following when you add a media set name and description:

BACKUP DATABASE [Inventory]

TO [InventoryBackup1], [InventoryBackup2], [InventoryBackup3]

WITH FORMAT, MEDIANAME = N'InventoryStripeSet',

MEDIADESCRIPTION = N'3 Devices = InventoryBackup1-3'

If you decide to use parallel striped backup to disk devices, you must make sure that each independent device has enough space to handle the appropriate portion of the database. Remember that files of filegroups do not always fill evenly. If one of the devices runs out of space, the backup will terminate.

After you have created a backup device or backup devices in a media set, you might want to save multiple backups to the same device(s). In fact, if you use the syntax to back up to a device without specifying any options other than the database and the devices to which you would like to back up, SQL Server automatically appends the new backup to the backup device. This has both advantages and disadvantages. If you back up the same database to a backup device three times over the course of a week, for example, and then you need to restore your last backup, you would probably do this:

On Monday, you execute the following:

BACKUP DATABASE [Inventory]

TO [InventoryBackup1], [InventoryBackup2], [InventoryBackup3]

On Wednesday, you execute the following:

BACKUP DATABASE [Inventory]

TO [InventoryBackup1], [InventoryBackup2], [InventoryBackup3]

On Friday, you execute the following:

BACKUP DATABASE [Inventory]

TO [InventoryBackup1], [InventoryBackup2], [InventoryBackup3]

The following Monday, you decide you want to restore the Inventory database to its state at the Friday backup, so you execute the following:

RESTORE DATABASE [Inventory]

FROM [InventoryBackup1], [InventoryBackup2], [InventoryBackup3]

And you restore Monday’s backup, not Friday’s. This mistake is one of the most common user errors when using multifile backups. To restore the appropriate backup, you must specify the backup based on position within the multifile backup device(s). To see the list of backups performed to a backup device(s), you can use the RESTORE HEADERONLY command, which has the following syntax:

RESTORE HEADERONLY FROM [InventoryBackup1]

This lists all of the backups (in this case, three rows) that have been performed to this device or these devices. You need to specify only one device because SQL Server can access the header from any of the devices of a parallel striped media set. After you list the header, you can use the position column to determine which backup must be restored. In this case the position is 3, so the restore command would use the following:

RESTORE DATABASE [Inventory]

FROM [InventoryBackup1], [InventoryBackup2], [InventoryBackup3]

WITH FILE = 3

If you would like to overwrite the contents of backup devices while backing up, you can do so by using the INIT option instead of appending. Because appending (NOINIT) is the default, you must specify when you want to overwrite. INIT only works when you are overwriting a media set with exactly the same format (which means the same number of backup devices) and when the backup set does not have a required retention period. If you want to break up a media set, which is changing the number of backup devices, you must use WITH FORMAT. If the backup set has a required retention period and you want to INIT the devices before the retention period has been met, you can tell SQL Server to SKIP the backup device headers.

Although many backup options are offered, not all are available for each individual backup method (for example, the password options are not available through SQL Server Enterprise Manager). For more details, spend some time testing and working with your own server. The options are as follows:

[[,] FORMAT | NOFORMAT]

[[,] MEDIADESCRIPTION = {'text' | @text_variable}]

[[,] MEDIANAME = {media_name | @media_name_variable}]

[[,] MEDIAPASSWORD = {mediapassword | @mediapassword_variable}]

[[,] NAME = {backup_set_name | @backup_set_name_var}]

[[,] DESCRIPTION = {'text' | @text_variable}]

[[,] PASSWORD = {password | @password_variable}]

[[,] INIT | NOINIT]

[[,] NOSKIP | SKIP]

[[,] EXPIREDATE = {date | @date_var}

| RETAINDAYS = {days | @days_var}]

[[,] STATS [= percentage]]

[[,] NOREWIND | REWIND]
[[,] NOUNLOAD | UNLOAD]
[[,] RESTART]

Some third-party hardware and tools, such as enterprise backup software or storage area network (SAN) devices, can back up your file systems and databases as well. This might simplify management in an organization because all backup-related work is standardized, bypassing learning syntax for each product you run. However, it is still helpful to know the syntax because it is essentially what is issued behind the scenes for whatever technology you employ.

Nevertheless, you must understand that a "normal" backup tool cannot just back up an active SQL Server database file. SQL Server must be accessed properly so that when the software starts a backup, SQL Server believes and responds as if a native backup is occurring. Otherwise, you might damage your databases, obtaining an inconsistent set of database files on restore. To enable SQL Server to believe a native backup is happening, device drivers and software must utilize the SQL Server Virtual Backup Device (VDI) API. You must check with your preferred software or hardware vendor prior to implementing or purchasing a solution to ensure that it not only works properly with your servers running SQL Server instances, but also supports the VDI. Similarly, if your system administrators use a program already for performing backups and now want to use it to back up your live SQL Server databases, verify that it is capable of doing so.

If you intend to use a third-party program that does not support the SQL Server VDI, one of the best options to integrate it into your SQL Server backup strategy is to have a SQL Server Agent job back up the database to a specified disk, and then have the program back up the SQL Server–generated file. Other variations on this theme obviously exist, but this example illustrates that you can leverage your existing backup solution even if it cannot back up your SQL Server databases directly. If the program does support the VDI, you might want to use it to manage your entire backup strategy. Mixing SQL Server Agent jobs or manual backups might interfere with your packaged solution. You should choose one strategy and stick to it.

Your storage vendor might provide technologies that greatly enhance your ability to back up and restore large amounts of data quickly, especially in a SAN environment. SQL Server 2000 supports these technologies through extensions to the VDI previously mentioned. Relative to SQL Server, a storage assisted backup is usually referred to as a split-mirror backup.

A new feature of Windows Server 2003 is the Volume Shadow Copy Service (VSS). It is a volume-oriented, snapshot-based backup. Windows Server 2003 also ships with something known as the SQLWriter, which is the back-end component of VSS that interacts with SQL Server. Under Windows, the writer is displayed as MSDEWriter. This can be verified by executing a VSSADMIN LIST WRITERS command in a command window.

SQLWriter is a VDI-based application that utilizes the VDI’s BACKUP WITH SNAPSHOT support. Because of the SQLWriter, you can use VSS with SQL Server 2000 to perform full backups. All recovery models are supported, and NORECOVERY allows you to roll the database forward. Differential, transaction log, and file backups are not supported with VSS. However, full database backups made with VSS can be combined with any other native backup strategy (such as transaction log backups or a third-party tool and so on) to handle the rolling forward after the VSS backup is restored to your instance.

When using the full database–based backup strategy, you always use a full backup to create the recovery set. This backup will be the first one used during the restore. To create a full database backup, you must use the BACKUP DATABASE command and all of the appropriate options. In the following example, a full database backup will be performed to three devices in parallel. The backup set will have a defined media set name, description, and password to protect the media set, a password to protect this specific backup, and the backup will be performed to tape devices so that the next backup can immediately append. Additionally, this backup will be set to prevent INIT for seven days and it will return the status of the backup every 10 percent backed up. The syntax for this command is:

BACKUP DATABASE [Inventory]

TO [InventoryBackup1], [InventoryBackup2], [InventoryBackup3] WITH FORMAT,

MEDIANAME = N'InventoryStripeSet',

MEDIADESCRIPTION = N'3 Devices = InventoryBackup1-3',

MEDIAPASSWORD = N'InventoryStripeSetPassword',

RETAINDAYS = 7,

NAME = N'InventoryBackup',

DESCRIPTION = N'Full Database Backup of Inventory',

PASSWORD = N'InventoryBackupFullDBPassword',

NOREWIND, STATS = 10

As the most important type of backup to perform, transaction log backups are critical to maintaining an optimal database size and allowing the full spectrum of recovery options. Transaction log backups—like all others—should be automated. In fact, to make a system highly available you will automate all of these backups to run at a consistent and frequent interval. Transaction log backups are the most frequent. In the upcoming section "Simplifying and Automating Backups," you will automate three different situations that trigger a transaction log backup to occur.

To create the transaction log backup you use a very similar command (BACKUP LOG) with many of the same options as BACKUP DATABASE used. To perform against the same three devices that the full database backup used, you submit the media set password. The media set password is required for all commands that need to access this media set. Without the password you will receive the error "Access is denied due to a password failure." To create an individual backup password for the transaction log backup, to leave the tape ready for another backup, and to return stats to know the status of the transaction log backup, use the command listed next.

BACKUP LOG [Inventory]

TO [InventoryBackup1], [InventoryBackup2], [InventoryBackup3] WITH NOINIT,

MEDIAPASSWORD = N'InventoryStripeSetPassword',

NAME = N'InventoryTLogBackup',

DESCRIPTION = N'Transaction Log Backup of Inventory',

PASSWORD = N'InventoryBackupTlogPassword',

NOREWIND, STATS = 10

Executing a differential database backup is actually exactly the same as executing a full database backup, except that you back up only the extents that have changed. You use the same command with one additional clause added: WITH DIFFERENTIAL. Following the same command as the full database backup (removing the media set definition parameters because they are necessary only on the first backup)—including STATS for progress and this time telling SQL Server to rewind and unload (unload implies rewind) the tape—use the following syntax:

BACKUP DATABASE [Inventory]

TO [InventoryBackup1], [InventoryBackup2], [InventoryBackup3]

WITH DIFFERENTIAL, NOINIT,

MEDIAPASSWORD = N'InventoryStripeSetPassword',

NAME = N'InventoryDiffBackup',

DESCRIPTION = N'Differential Database Backup of Inventory',

PASSWORD = N'InventoryBackupDiffPassword',

UNLOAD, STATS = 10

Creating a production SQL Server Agent backup job is deceptively simple.

The specific details for each of the seven steps found in the Visio diagram are provided here.

The details of the steps as seen in the SQL Server Agent are shown in Figure 10-9.

Figure 10-9. SQL Server Agent backup job.

When a backup job is being run using SQL Server Agent, it is tough to see how complete the job is as it is running because there is no graphic representation. However, if you use the STATS option with the BACKUP command, you can see the percentage completed using the DBCC OUTPUTBUFFER command against the session performing the backup. The percentage complete is shown in the right-hand column of the output.

To see the percentage completed you must first know the system process ID (SPID) of the backup process. To get the SPID of the backup use the following query:

SELECT DISTINCT(spid)

FROM master.dbo.sysprocesses (nolock)

WHERE cmd LIKE 'BACKUP%'

Output:

spid

------

742

Next, use the DBCC OUTPUTBUFFER command with the backup process’ SPID:

DBCC OUTPUTBUFFER(742)

When reviewing the output, the far right column shows the character values for the hexadecimal output. This is where you can see the last percentage returned by the job. The following is an example output showing that the backup is at 3 percent:

Output Buffer

-------------------------------------------------------------------------------

00000000 04 00 00 5d 02 e6 03 00 79 01 00 00 00 ab 44 00 ...].μ..y....½D.

00000010 8b 0c 00 00 01 00 14 00 33 00 20 00 70 00 65 00 ï.......3. .p.e.

00000020 72 00 63 00 65 00 6e 00 74 00 20 00 62 00 61 00 r.c.e.n.t. .b.a.

00000030 63 00 6b 00 65 00 64 00 20 00 75 00 70 00 2e 00 c.k.e.d. .u.p...

Once you have performed your backups and likely automated them, it is critical that you periodically test that your process and strategy is occurring. These verification procedures do not guarantee that a restore will be successful, but they do guarantee that you have the backups you are expecting to have.

In the interim, there are four ways you can verify your backups:

The file creation phase is only performed when the file or files of the database being restored do not already exist in the appropriate size or location. The location can be changed during restore (RESTORE WITH MOVE) but the size cannot. If the database was defined at a total size for data of 10 GB, yet the backup yielded only a 300 MB full database backup because the database does not currently have a lot of data, it does not matter. The database created during the restore must completely resemble the database structure as it was backed up. The structure for all files and filegroups cannot be changed during restore. If you are restoring in place or if a subset of files is damaged, be sure not to drop the database. If the files are already there, you can save time during the restore. Because speed is the key to recovery in high availability, this is the first step to improving the restore performance. Even if the database is suspect and one or more of the files are damaged, you can still restore that database in place, replacing the files that are damaged (specifying their new location using RESTORE WITH MOVE).

The second phase that a restore goes through is the media copy phase. During this phase, only faster hardware can help. SQL Server reads the pages from the backup, determines their page location (from the page header), and writes the pages to their appropriate location on disk. Even if the pages are logically inconsistent (see "How Do Full Database Backups Work?" in Chapter 9), this phase is uninterested. The sole purpose of this phase is to copy from the backup medium as fast as possible.

Again, focusing on speed, there are a few options. If you performed your backup to a parallel striped media set then your restore is also performed in parallel. If your database files being recovered are on faster RAID arrays, this can improve the copy phase (that is, striped mirror or mirrored stripes are faster than RAID 5; however, you will need to know how your hardware behaves, including things like the write cache, and so on).

Finally, maybe you can restore less. In the case of isolated corruption for a database that uses multiple files or filegroups, you can restore just the files or filegroups that are damaged.

The two final phases a backup goes through during restore relate to the transactions that must be applied. This occurs in almost every type of backup with the exception of file/filegroup restores (the transaction log is not backed up during these backups). Because all other backups include some portion of the transaction log or are solely transaction log backups, the restore must use the transaction log to recover what is not already in the database. To do this there are two phases of log analysis: redo and undo.

During the redo phase, SQL Server reviews the log to apply any transactions not already in the database. Because some of the transactions might not have committed by the time the backup completed the final phase, undo must be performed. However, the undo phase is actually determined by the RESTORE command’s recovery completion state. If more backups are to be restored, then there is no reason to undo. Undo is only performed for the final restore, which is the restore that is meant to bring the database online for users. Determining when to recover the database is based on the recovery completion state specified during the restore.

When each backup is restored, a recovery completion state must be defined. The recovery completion state defines the state in which the database will be when that particular backup is restored. More specifically, because each backup is an image of the database as it looked when the backup completed, SQL Server must determine the fate of the transactions that were pending when the backup completed. With each option you accomplish something different—for different purposes. The recovery completion states are RECOVERY, NORECOVERY, and STANDBY.

RESTORE DATABASE DatabaseName WITH RECOVERY

To perform database recovery in place (that is, replacing the existing—and damaged—database), you will need to make sure that there is a place for all files required for this database. To see the list of files for a database, you can use RESTORE FILELISTONLY. This tells you the number of and location for all files in the database. If drives are damaged and no space exists on other drives (which are large enough for the file), then the database cannot be restored until damaged devices are replaced. If devices are damaged, yet other drives have plenty of space, the restore can "move" the files from their damaged location to a new location during the restore.

If you are running with the file-based backup strategy, the recovery process is more complex but is likely to be faster in the case of isolated hardware failure. With the full database–based backup strategy the easiest option is to recover the entire database and roll forward using other backups. The restore of the full database backup could take a significant amount of time. If you have a database designed using multiple files and filegroups, then you can restore only the damaged files. Better yet, if you have chosen to backup the individual files and filegroups then the recovery process is more granular as well. In place, you can restore only the damaged files and filegroups, restore the differentials for the files and filegroups restored, and then (and only then) apply the correct sequence of transaction log backups to get up to the minute. Of this entire process, finding the correct sequence of transaction log backups to apply is the most challenging stage. Above all, it requires an understanding of how to read backup history information from backup devices or preferably from msdb backup history tables.

Using the database backups showing the file-based backup case study earlier in this chapter in the section "Executing the File-Based Backup Strategy Using Transact-SQL," what happens if there is a disk failure on the file c:Program FilesMicrosoft SQL ServerMSSQLDataFileBasedBackupDBRWFile2.NDF? If this file is not accessible, SQL Server marks the database as suspect, as shown in Figure 10-10.

Figure 10-10. FileBasedBackupDB: A suspect database.

The entire database is inaccessible, so how can you determine the exact cause of error? Review the SQL Server error log, which can be found as a text file in the LOG directory. Additionally, you can find these messages in the Application Log of Event Viewer. Or, if SQL Server Enterprise Manager is accessible, you can obtain the graphical version of the error log. In the error log, you should be able to find a data file error for the damaged file, as shown in Figure 10-11.

Figure 10-11. FileBasedBackupDB: Data file error.

By double-clicking on the error you can see even more details, as shown in Figure 10-12.

Figure 10-12. FileBasedBackupDB: Specific file error.

At this point, you know exactly what is damaged. You have file- and file-group-based backups, so the recovery process can be performed with just the damaged files. The recovery process always begins with a backup of the tail of the transaction log. In this case, you execute:

BACKUP LOG [FileBasedBackupDB]

TO [FileBasedBackupDev]

WITH NAME = 'FileBasedBackupDB Backup',

DESCRIPTION = 'FINAL Transaction Log (Tail up-to-minute)', NOINIT, NO_TRUNCATE

Once the tail of the transaction log is backed up you have the set of backups shown in Figure 10-13.

Figure 10-13. File corruption of RWFile2 between points in time 12 and 13.

Because only RWFile2 is damaged, only that file needs to be recovered. To recover this file you need to first find the last "full" version of this file backed up. At point in time 3, the entire filegroup was backed up. This is your starting point for this restore. Beginning with the filegroup backup at position 3, you can restore just this damaged file using:

RESTORE DATABASE FileBasedBackupDB

FILE = 'FileBasedBackupDBRWFile2'

FROM [FileBasedBackupDev]

WITH FILE = 3, NORECOVERY

To move this file to a forward point in time, use the last differential for this file, which was performed at point in time 11. To recover just this file from this differential, the syntax is exactly the same with the exception of pointing SQL Server to the correct differential backup. To recover the differential at position 11 use:

RESTORE DATABASE FileBasedBackupDB

FILE = 'FileBasedBackupDBRWFile2'

FROM [FileBasedBackupDev]

WITH FILE = 11, NORECOVERY

Finally, transaction logs need to be applied. This is the most challenging part about recovering from file and filegroup backups. Because transaction log backups can occur concurrently, multiple transaction log backups could "overlap" with a file- or filegroup-based backup. To restore the correct transaction logs you must determine the minimum effective log sequence number (LSN). In other words, you must review the earliest point that needs to be restored. In this case, because it was isolated failure, there is only one file affected. The last restore was for point in time 11. Review the backup history with the following query:

SELECT Backup_Start_Date, [Name], [Description],

First_LSN, Last_LSN, Backup_Finish_Date

FROM msdb.dbo.backupset AS s

 JOIN msdb.dbo.backupmediafamily AS m

 ON s.media_set_id = m.media_set_id

WHERE database_name = 'FileBasedBackupDB'

ORDER BY 1 ASC

The output returned for all of the backups (the backup number refers to the backup as shown by the case study) for just the First_LSN and Last_LSN columns is shown in Table 10-2.

Reviewing the First_LSN column for backup set number 11, you can see that the minimum LSN is 22000000192900002. To determine the appropriate transaction log to restore, you must find the first transaction log that includes this LSN. Review only transaction log backups by adding AND type = ’L’, as well as adding the appropriate value for the First_LSN. The value of Last_LSN should be less than or equal to the First_LSN of the next backup set, which is greater than the value for Last_LSN of the existing backup set. If you follow this rule and add these to a WHERE clause in the SELECT statement shown earlier, you can determine the proper transaction log to restore. Below is an example:

WHERE database_name = 'FileBasedBackupDB'

AND type = 'L' AND First_LSN <= 22000000192900002

AND Last_LSN >= 22000000192900002

Once executed, it becomes clear that the backup you will restore is the backup at position 12 (in this case the backup is after the last differential; although this is likely, it is not guaranteed). The good news is that if you accidentally restore a transaction log that is too early (for example, the transaction log backup at position 10), SQL Server generates this error message:

Server: Msg 4326, Level 16, State 1, Line 16

The log in this backup set terminates at LSN 21000000305600001, which is too
early to apply to the database. A more recent log backup that includes LSN
22000000193600002 can be restored.

Server: Msg 3013, Level 16, State 1, Line 16

RESTORE LOG is terminating abnormally.

Along the same lines, if you restore a transaction log that is too late then SQL Server also gives a comparable message:

Server: Msg 4305, Level 16, State 1, Line 1

The log in this backup set begins at LSN 24000000031500001, which is too late to
apply to the database. An earlier log backup that includes LSN 22000000193600002
can be restored.

Server: Msg 3013, Level 16, State 1, Line 1

RESTORE LOG is terminating abnormally.

Executing NORECOVERY only with file 12 leaves the transaction log at position 12. To restore the last set of transaction logs and recover the database, you complete the restore with the last two transaction log restores:

RESTORE LOG FileBasedBackupDB

FROM [FileBasedBackupDev]

WITH FILE = 12, NORECOVERY

RESTORE LOG FileBasedBackupDB

FROM [FileBasedBackupDev]

WITH FILE = 13, RECOVERY

Once the database is restored with RECOVERY then no additional restores can be performed. Additionally, users are allowed back into the database.

The following examples assume that you are restoring to the same database you backed up from, or you have also restored the master database prior to starting the restore. The reason for this is that the user IDs will not be in sync if you restore to a different server than the one users were created on.

For more information on synchronizing users, see the topic "Transferring Logins, Users, and Other Objects Between Instances" in Chapter 14.

There are times when it is not necessary to recover a complete log. In fact, you might not want to because the complete transaction log has information in it that you do not want applied to the database. A good example of this is that someone accidentally forgot to provide a WHERE clause on a DELETE statement and wiped out a critical table.

If you know what time the person deleted the rows from the table, you can recover all the way up to the moment before it happened. SQL Server provides this functionality using the STOPAT syntax. In the following situation, this part of RESTORE is explored:

-- restore the full backup made at 11AM

restore database dev_db from full_backup with norecovery

go


-- restore the 11:15AM log backup

restore database dev_db from log_backup with file=1, norecovery

go


-- restore the 11:30AM log backup

restore log dev_db from log_backup with file=2, norecovery

go


-- restore the 11:45AM log backup

restore log dev_db from log_backup with file=3, norecovery

go


-- restore the noon backup, but STOP recovery at 11:49AM, just before

-- the table was deleted at 11:50AM

restore log dev_db from log_backup with file=4, recovery, STOPAT = 'Jan 28, 2003
11:49:00.000 AM'

go

There are times when you are restoring a database that you need to move the data to a new location. Common examples of this include the following:

SQL Server provides the MOVE syntax for this purpose. You need to know the logical names of the files you are going to move. To get this information, run the command RESTORE FILELISTONLY against the backup. This lists the logical names. After that, you only need to do a restore and provide the new target locations.

In the following example, a production database is to be restored to a test server from an LTO array. The database has 12 data files and one log file. Due to the size of the database, you want to monitor the progress, so you have added the STATS syntax to show the percentage complete.

-- Examine the logical and physical locations on the backup

restore filelistonly from LTO1, LTO2, LTO3, LTO4

go


-- First, create the new directories on the file system so we can

-- RESTORE to them.


-- Now, restore the database as you normally would, except

-- specify to SQL Server the new locations for the data devices



restore database test_db

from LTO1, LTO2, LTO3, LTO4

with

move 'data1' to 'H:mssqlDATADATA1.MDF',

move 'data2' to 'I:mssqlDATADATA2.NDF',

move 'data3' to 'J:mssqlDATADATA3.NDF',

move 'log1' to 'U:mssqlLOGLOG1.LDF',

recovery, stats=1

To create a backup using the Backup tool, follow these steps:

To restore a backup using the Backup tool, follow these steps: