SQL : Performing Point-in-Time Recovery

SQL : Performing Point-in-Time Recovery

Pre-requisites:

  • Requires full recovery model on database
  • Requires a full backup
  • Requires Transaction Log backups to be configured (at a frequency of your choice)

The following TSQL will enable you to perform point-in-time recovery on an SQL database:

/* SQL Point-in-time recovery script */
/* Uses the last full back, then TRN files from the last backup. */

/* Restore the last Full backup */
RESTORE DATABASE [POTRecovery]
FROM DISK = N’D:\MSSQL$POTRecovery\Data\Backup\POTRecovery.bak’
WITH FILE = 1, NOUNLOAD , STATS = 10, NORECOVERY

/* Restore the TRN backups, using the same point-in-time for each */
/* Date format is YYYY-MM-DD HH:MM:SS – 24Hr clock */

RESTORE LOG [POTRecovery] FROM DISK = N’D:\MSSQL$POTRecovery\Data\Backup\POTRecovery_tlog_201003051015.TRN’
WITH FILE = 1, NOUNLOAD , STATS = 10, RECOVERY , STOPAT = N’2010-03-05 10:20:00

RESTORE LOG [POTRecovery] FROM DISK = N’D:\MSSQL$POTRecovery\Data\Backup\POTRecovery_tlog_201003051016.TRN’
WITH FILE = 1, NOUNLOAD , STATS = 10, RECOVERY , STOPAT = N’2010-03-05 10:20:00

RESTORE LOG [POTRecovery] FROM DISK = N’D:\MSSQL$POTRecovery\Data\Backup\POTRecovery_tlog_201003051024.TRN’
WITH FILE = 1, NOUNLOAD , STATS = 10, RECOVERY , STOPAT = N’2010-03-05 10:20:00

/* Now set the database into a useable mode – without this the database will be stuck in a ‘Loading’ state */
RESTORE DATABASE  [POTRecovery] WITH RECOVERY

Using a third party backup tool and SQL-based TRN backups you can still recover as above, just ensure you perform the database restore using Data Protector then use SQL Management Tools to perform the point-in-time recovery.