SQL Server 2008: Restore database to a point of time

Posted: June 30, 2011 in Database
Tags: , ,

Last week, one of my colleague executed an update statement without condition in one critical table. As a result, we must perform a database restore.

Fortunately, the corrupted database was set to FULL recovery model. To apply a transaction log backup, the database must have been set to FULL or bulk-logged recovery model.

To set the database to FULL recovery model.

ALTER DATABASE database_name SET RECOVERY FULL

Below is the statement to restore the database back to 2011-06-24 16:00:00.

RESTORE DATABASE database_name;
FROM DISK = 'D:\backup\database_name_full_backup_24hour_ago.bak'
WITH NORECOVERY

RESTORE LOG database_name;
FROM DISK = 'D:\backup\database_name_transaction_log_backup.trn'
WITH NORECOVERY, STOPAT = '2011-06-24 16:00:00'

RESTORE DATABASE database_name WITH RECOVERY

The first step is to restore the database to the full backup 24 hour ago.

The next step is to apply the transaction log backup and restore the database to 2011-06-24 16:00:00.

WITH NORECOVERY omits the undo phase to preserve uncommitted transactions. Omitting the undo phase allows for restoring other backups to roll the database further forward in time.

The last step is to conclude the restore.

WITH RECOVERY includes both the redo and undo phases and recovers the database; additional backups cannot be restored. This is the default. If the whole roll forward set is consistent with the database, recovery is performed, and the database can be brought online.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s