SQL Server: Point in Time Recovery Example

Posted at Thursday, May 29, 2008
This example demonstrates how to backup a SQL Server Database and then restore it to a specific point in time.

The database in question uses the FULL recovery model, which supports such a PITR (Point in Time Recovery). The example uses a simple table to illustrate how all this works.

The example first creates a sample table and performs a full backup on the database. Then, values are inserted into the table periodically, interlaced with transaction log backups.

Set-up the example by running PITRBackupExample.sql.

The script yields the following output.

In this example, I want to restore the database to the point at which the 'Point-in-time I want to recover to!' value was inserted into the sample table. To do this, a restore point just after this transaction, specifically the '2008-05-28 11:58:09.680' timestamp, will need to be specified.

The recovery first places the database in single-user mode and performs a tail-log transaction log backup. Then, the full backup and transaction log backups are applied. For the last transaction log backup the STOPAT option is specified, along with the WITH RECOVERY option, which denotes that this operation is to end the recovery. Afterwards, the database is placed into multi-user mode. Last, we check the sample table to make sure that we recovered successfully to the desired timestamp.

Recover the example by running PITRExample.sql

The script yields the following output

Labels: ,