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.