Restoring WITH STANDBY
As useful as the STOPAT option can be, it's not perfect. What happens if you know that data was destroyed, but you don't know exactly when it happened? For example, you discover at 5:00 p.m. that a crucial table was dropped during the day, but you don't know when. You'd like to restore the database to a time as close as possible to the time the data was damaged.
In a typical restore, as I mentioned earlier, you have the choice of either specifying WITH RECOVERY to roll back incomplete transactions or specifying WITH NORECOVERY. If you use WITH RECOVERY, you can't restore subsequent log backups, but the database is fully usable. If you run WITH NORECOVERY, the database might be inconsistent, in which case SQL Server won't let you use it.
But what if you had a way to combine the two approaches by restoring one log backup, then looking at the data before restoring more log backups? Such a combined approach would be particularly helpful if you're trying to do a point-in-time recovery but you don't know what the right point is.
SQL Server provides an option called STANDBY that lets you recover the database and still restore more log backups. If you restore a log backup and specify
WITH STANDBY = '<some filename>'
SQL Server rolls back incomplete transactions but keeps a record of the rolled-back work in a specified file called an undo file. The default suffix for this undo file is .ldf because its structure is just like the transaction log's structure, which also uses the .ldf suffix.
The next RESTORE LOG operation reads the contents of the undo file, redoes the operations that were rolled back, then restores the log backup specified in the RESTORE LOG command. If that RESTORE LOG command also specifies WITH STANDBY, the restore again rolls back incomplete transactions but saves a record of those rolled-back transactions. After each RESTORE LOG ... WITH STANDBY operation, the data is consistent because no half-completed transactions are included in the database, so users can access the database and read the data. Thus, you can determine after restoring each log whether a particular change has already taken place. (Keep in mind that you can't modify any data if you've restored WITH STANDBY—SQL Server generates an error message if you try. But you can read the data and continue to restore more logs if you want to.) You must restore the final log WITH RECOVERY (and SQL Server won't keep an undo file) to make the database fully usable.
You can use the RESTORE WITH STANDBY option to try to track down the time that data was damaged, but the process isn't fun. After you restore a log and discover that it contains the undesired operation, you have to try to narrow down the time within that span of log records when the damage occurred. You need to go all the way back to the beginning of the restore process and use the STOPAT option to stop at some point in the middle of the time that the log backup spans. If you examine the data and it's still good, you know the damage happened at a later time; if the damage already shows up, you know the change happened at an earlier time than the middle of the log backup. You repeat this bisecting process, stopping either a little later or a little earlier in the log each time. This tedious process can help you recover to a time right before your data was damaged so that you minimize your data loss.
As an alternative, you can use Lumigent Technologies' Log Explorer 2.5, which can show you all the records in the transaction log and the time each transaction was initiated. You can use the transaction datetime information to determine the point to specify in your STOPAT option, or you can use Log Explorer's facilities to reverse the transaction. But be cautious about using Log Explorer to undo a change unless you have no other option; other changes might depend on the one you're trying to undo. If possible, use Log Explorer for information only and use SQL Server's restore capability to bring the databases back to a desired state.
Partial Restore
SQL Server 2000 lets you perform a partial restore of a database in emergencies. Although the description and the syntax of a partial restore are similar to those of file and filegroup restores, the operations are quite different. For restoring file and filegroup backups, you start with a complete database and replace one or more files or filegroups with previously backed-up versions. (For details about file and filegroup backups and restores, see the sidebar "Backing Up and Restoring Files and Filegroups.") For a partial database restore, you don't start with a full database. You restore individual filegroups (including the primary filegroup, which contains all the system tables) to a new location. Any filegroups you don't restore no longer exist, and SQL Server refers to them as offline when you attempt to reference data stored in them.
After a partial restore, you can restore log backups or differential backups to bring the data in the restored filegroups to a later point in time. A partial restore gives you the option of recovering the data from a subset of tables after an accidental deletion or modification of table data. You can use the partially restored database to extract the data from the lost tables, then copy the data back to your original database. (For more information about partial restores, see "The Road to Recovery," September 2001, InstantDoc ID 21627.)
Prepare for the Extraordinary
You can choose from many options when restoring a database, and you have to remember lots of details. When your restore operations are successful, they seem easy and straightforward. However, if you want to do anything unusual with your restore or if the process doesn't work as you expect, knowing what SQL Server is doing during the restore process and what SQL Server expects from you can help prepare you for some of the possible extraordinary circumstances. Start now to develop your step-by-step recovery plan, and test it to make sure it behaves as expected.
End of Article
Prev. page
1
2
3
[4]
next page -->
