Introduction
One of the most important responsibilities of every DBA is to
restore data in the event of a disaster. The disaster may be due to
hardware failure or to a catastrophic event such as flooding that
destroys the server. A more common disaster is human error:
the accidental deletion or modification of data.
SQL Server provides "point in time" recovery, allowing a database
to be restored up to the point of the accidental modification or deletion.
Requirements
The database's recovery model must be set to "full". If
the recovery model is set to "simple", transaction log backups are not possible,
and you will only be able to restore full and differential backups.
If the recovery model is set to "bulk-logged", you can restore transaction log
backups, but not to a point in time. To determine the recovery model setting
view the "Options" tab
of the database properties dialog box or run the following command:
select DATABASEPROPERTYEX('<database name>', 'Recovery')
The property can be changed by modifying the setting in the property dialog box or
by using the "Alter Database" command.
Figure 1: Recovery Model
The next requirement is obvious: a recent full backup must be available.
Many shops backup databases to disk using SQL native backup or with a tool like SQL
LiteSpeed. The backup files are generally copied to tape and shipped
to an offsite storage facility. This strategy speeds up the recovery
process. You not have to wait for a tape to be returned from storage, and
it is also quicker to perform the restore from disk than it is from tape.
Finally, you must have all of the transaction log backups past the
point at which you want to restore. Since the tape backup is probably only
performed once each day after the full backup is completed, it may make sense to
copy transaction log backups as they are created to another server.
Copying the files to another server is not required for point in time recovery,
but it is valuable in case of hardware failure.
During the restore process, the database will be unavailable. You must
restore all transactions up to the point in time, you cannot restore
only the accidental transaction.
Point In Time Recovery Using Enterprise Manager
If the backups are recorded in the backup history, it is very
easy to perform the point in time recovery using Enterprise Manager.
It is possible to load the backup sets into the history from a different server -- one at a time -- using the "Restore Dialog" box from a device with the
"Read backup set and add to backup history" option. You can also use the
"RESTORE VERIFYONLY" command to load the backups into the history. If you go to
this much trouble, you may want to just script the restore. The following instructions
assume that the backups are recorded in the backup history.
- In Enterprise Manager, right-click on the database to be restored.
- Select "All Tasks" and then "Restore Database".
- Choose the time of the "First Backup to Restore".
- Uncheck transaction log backups that so that the last one that is included
is past the desired recovery point.
- Check "Point in time restore".
- Type in the date and time in the dialog that pops up for the point at which the
restore process should stop.
- Make sure that the "Recovery completion state" found on the "Options" tab is
set at "Leave database operational..." which it should be by default.
- Review the settings and click "OK" to perform the restore.
Figure 2: Using Enterprise Manager
Point In Time Recovery Using a Script
Here is a sample restore to a point in time script:
RESTORE DATABASE [PointInTime] FROM DISK = N'e:\sqldata\MSSQL\BACKUP\PointInTime_db_200505022127.BAK'
WITH FILE = 1, NORECOVERY
RESTORE LOG [PointInTime] FROM DISK = N'e:\sqldata\MSSQL\BACKUP\PointInTime_tlog_200505022130.TRN'
WITH FILE = 1, RECOVERY , STOPAT = N'5/2/2005 9:34:20 PM'
RESTORE LOG [PointInTime] FROM DISK = N'e:\sqldata\MSSQL\BACKUP\PointInTime_tlog_200505022135.TRN'
WITH FILE = 1, RECOVERY , STOPAT = N'5/2/2005 9:34:20 PM'
The "NORECOVERY" option allows you to restore additional logs after the full
backup is complete. If you have
multiple log files to restore, you can use the "RECOVERY , STOPAT " options in each log restore command. If the
stopping time is too early for a particular file, the entire log will be applied. When the point in time is reached, the database is recovered. Be careful here:
if you intend to restore log files without restoring to a point in time, be sure to use the
"NORECOVERY" option in all but the last transaction log to restore.
If you use a third-party backup solution, be sure to review the documentation for
the commands needed to do point in time restores.
Conclusion
The knowledge required to do a point in time restore is kind of like your car insurance policy:
you hope you never have to use it! Don't wait until disaster strikes. Make sure you are ready to restore your production databases at a moment's notice, including to a point in time.
