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.