When you use the file and filegroup backup strategy, you must make sure that you back up every file so that you create a complete backup set (all of the database's data files) and can recreate the database framework if needed. You can create a backup set either by backing up the files individually, by backing up filegroups, or by backing up some combination of the two—as long as all files are backed up at some point. In SQL Server 2000, you can back up any file individually—even when it's a member of a filegroup. In SQL Server 7.0, you must back up the entire filegroup; you can't separately back up individual files that are members of a filegroup. This change in SQL Server 2000 gives much better backup-and-restore granularity: If an isolated failure occurs, you have to restore only that file. However, finding the file's pages in a larger filegroup backup takes more time than restoring the file from an individual file backup.
The PubsTest case study provides the database framework along with the most recent file backup for the primary file (backup 9) and backups of the filegroups (backups 3 and 5). The example recovery scenario involves recovering the entire database. However, one of the biggest benefits of the file and filegroup backup strategy is the ability to recover quickly when isolated corruption or media failure occurs. Instead of recovering the whole database or recovering partially from a full database backup (which is slower because the backup contains the entire database, not just the target file or filegroup), you can recover just the corrupted file or filegroup. To determine the correct strategy for recovering from isolated corruption, see the sidebar "Restoring After Isolated Disk Failure," page 28, which contains syntax, examples, and some important recommendations.
To recover the entire PubsTest database, you build the database framework, restoring the last full file and filegroup backups first. To build the example framework, you'd restore backup number 9 to create the primary file, then backup 3 to create the RWFG filegroup, then backup 5 to create the ROFG filegroup. Because the database recovery process is incomplete (the database files are all at different states and still need to be rolled forward to a consistent point), the database is said to be in a loading state. To continue to apply transaction logs (to get the database files to the same point in time), use the NORECOVERY option when you restore all file and filegroup backups. This option lets you restore backups without bringing the database online until it's recovered. When you're ready to bring the database online, you can use the following simple statement:
RESTORE DATABASE dbname WITH RECOVERY
Before you get to that point, you need to roll forward the data to the point in time when the disaster occurred. To minimize rollforward time, you might have been performing differential backups in addition to transaction log backups. Differential backups can occur at any level: database, file, or filegroup. The next step on the road to recovery is to restore the last differential backups of all files or filegroups. In the case study, you have a differential backup for RWFG at backup number 7 and again at backup 11. Because differentials contain all changes made since the last full file or filegroup backup, you need to restore only backup 11. (If the differential backup failed, you could easily use the next most recent differential backup, so you have some redundancy in your backup strategy.)
Now you have the bulk of the database populated. However, the database is still not ready for recovery. The files are still at different stages of modification: The primary file is at point in time 9, RWFG is at 11, and ROFG is at 5. The next step is to roll forward the database by applying the correct transaction log backups. Watch out—this step can be tricky. To determine the proper sequence of log backups to apply, you need to first figure out the oldest backup set that you've restored. In the case study, you restored the following:
- Backup set number 9 to get the latest full file backup of the primary file
- Backup set number 3 to get the latest full filegroup backup of the RWFG filegroup
- Backup set number 5 to get the latest full filegroup backup of the ROFG filegroup
- Backup set number 11 to get the latest differential of the RWFG filegroup
At this point, the ROFG filegroup is at the earliest point in time—point in time 5. To recover up to the minute, you need to calculate the minimum effective log to apply. For this, you can use the information from the backup history. Querying msdb for backup history can help speed the process; however, msdb might not be available at the time of the failure. If msdb is available, you can query it directly to gather the information about your database backups. Querying backup history from msdb can produce quite a bit of information because SQL Server doesn't automatically clear msdb's backup history. Historical information is kept permanently. You might want to periodically clear old information from msdb, but make sure to retain one or two complete backup sets of information. To clear the backup history, you can use the msdb.dbo.sp_delete_database_backuphistory or msdb.dbo.sp_delete_backuphistory procedures (see SQL Server 2000 Books Online—BOL—for syntax and parameters).
If msdb is unavailable, you must gather the backup history by reviewing the header information directly from your backup devices. To view the header information, use the LOAD HEADERONLY syntax. Web Listing 1, downloadable from http:\\www.sqlmag.com, contains some examples of how to use this syntax. LOAD HEADERONLY requires that the backup devices be online. Bringing all the tape devices online and gathering all of this information will probably add quite a bit of time to your recovery process. You'll probably load some backup devices and inevitably interrogate some of the wrong ones to find the correct set and the right sequence to restore. To ensure that msdb is always available for querying, see the sidebar "Msdb Quick Tip" for instructions on resetting msdb to allow transaction log backups and how to back it up frequently.
In the PubsTest case study, you can review the backup set information by using the query that Listing 1 shows. Executing this query produces the results that Table 1 shows. These results are fairly clean because the backups used good naming conventions. I recommend that you have a standard for backup names and descriptions so that you can more quickly find the correct sequence of backups to restore. In the case study, I used descriptive names, such as "PubsTest Backup, File = pubs" and left the backup description option blank. Additionally, the backup history was cleared before these backups occurred.
Now, the database framework has been restored and you know that ROFG is at the earliest state in this database's backup set. To roll the database forward up to the minute of the disaster, you need to restore the transaction logs in the correct sequence. To determine the correct set of transaction log backups to restore, you need to find backup 5's minimum effective log sequence number (LSN), which the First_LSN column in Table 1 contains. In this case, the minimum effective LSN of backup number 5 is 13000000248600001. To know which logs to restore, find the first transaction log whose minimum effective LSN is less than this number—backup number 6, in this case. You need to find the next lower LSN because you're looking for the transaction log (or logs) that contains the transactional information of what happened during this backup. Because log backups can occur concurrently with file and filegroup backups, multiple log backups could occur within the time frame of a large file or filegroup backup. In some cases, the first transaction log to load might be "before" the file or filegroup set you've loaded. So, always review the LSNs to ensure that you have the proper starting point.
To complete the full recovery of this database, you need to apply all transaction logs starting with backup number 6. In this case, you'd restore backup 6, then 8, then 10, then 12, and finally backup 13 to bring the data up to the minute. For backup number 13 (the last transaction log), you can either use the RESTORE WITH RECOVERY option to bring the database online or use the NORECOVERY option, then follow this restore with a RESTORE DATABASE PubsTest WITH RECOVERY statement to recover the database.
Be Prepared
Recovering the entire database from file and filegroup backups is by far the most complex backup and restore strategy. This strategy takes the greatest amount of testing to fully understand and requires good practices such as strong naming conventions and regular backups of msdb to ensure an easy recovery. But by using this strategy, you can completely recover a database without ever doing a full database backup or having to pause your log backups. With this strategy, you can selectively choose when to back up large read-only portions of your database, an approach that can save backup time and money in backup media. More important, because the log is never paused, your secondary sites (for example, log-shipping destination sites) will always receive the transaction logs as quickly as possible, which can drastically reduce data loss in the event of a site failure. For best results, have your most senior DBA devise the backup plan and the least senior DBA test it.
End of Article
Prev. page
1
2
[3]
next page -->
