How Do I Recover To A Point in Time?

Occasionally someone executes a bit of code that causes you to do a database restore—for example, if someone inadvertently drops a production table or forgets the WHERE clause in a DELETE query. As a result, you need to restore to a point in time just before the offending code executed.

Recovery is the set of operations that makes a database consistent at startup. To restore to a point in time, you must be using either full or bulk-logged recovery. The simple recovery model causes the transaction log to be truncated on checkpoint, with no redo-undo recovery and no way to restore to a point in time.

The goal of recovery and “roll forward/roll back” is to restore the data to its original state at the recovery point, which is a user-specified point in time, a named transaction, or a log sequence number. The bulk-logged recovery model has one additional limitation: You can perform a point-in-time recovery only if you haven’t performed any bulk operations since the previous log backup. In other words, to recover to a point in time, you must have an unbroken sequence of log files.

The recovery point in time will be within a transaction log backup. When restoring this log, you can recover transactions that came before your point in time by specifying the recovery point in a STOPAT, STOPATMARK, or STOPBEFOREMARK clause.

When restoring a database to a point in time, perform a full backup with the NORECOVERY option, as follows:

RESTORE DATABASE AdventureWorks
  FROM DISK =  'E:SQLdataBACKUPSAdventureWorks_FullDbBkup.bak'  
  WITH  NORECOVERY

Then, apply each of the transaction log backups with the RECOVERY option, specifying the point-in-time date and time on each RESTORE LOG statement, as follows:

RESTORE LOG AdventureWorks
  FROM DISK =  'E:SQLdataBACKUPSAdventureWorks_TlogBkup.bak'
  WITH RECOVERY,  STOPAT = ‘ Dec 10, 2007 8:10 PM’