Point-in-time recovery allows to restore a database into a state it was in any point of time. This type of recovery is applicable only to databases that run under the full or bulk-logged recovery model. If the database runs under the bulk-logged recovery model and transaction log backup contains bulk-logged changes, the recovery to a point-in-time is not possible.
To understand how point-in-time recovery works let’s review the use case with a sample database (under the full recovery model) where a full backup is made every 24 hours and a differential database backup that is created every six hours, and transaction log backups are created every hour. Please see the picture below:
Assume some crucial data were deleted at 13:30 and the last transaction log backup was made at 14:00. The transaction log backup from 14:00 gives us the opportunity to restore the database to its state at 13:29:59. In this case, only 30 minutes of updates will be lost.
Below we will consider two ways of restoring a database to a point-in-time:
Using Transact-SQL
To recover a database to a point-in-time it is necessary to start from restoring the full database backup using the following syntax:
RESTORE DATABASE your_database FROM DISK = 'full_00_00.bak' WITH NORECOVERY, REPLACE
After the full backup is restored it is time to restore the last differential backup. It our case – the last differential backup was made at 12:00. Differential backup can be recovered with the help of this syntax:
RESTORE DATABASE your_database FROM DISK = 'diff_12_00.bak' WITH NORECOVERY
This differential backup includes all changes that were made to the database since the last full backup.
And the last step is to apply all transaction log backups that were made after the differential backup. Please apply all transaction log backups in the same sequence in which they were created. It is crucial to remember to specify the time to which database should be restored with the help of STOPAT option when you restore the last log backup (in our case the database should be restored as of 13:29:59). Please also note that all backups, except the last one, are restored with NORECOVERY option, while the last one should be restored with RECOVERY option recovering the database into its working state:
RESTORE LOG your_database FROM DISK = 'log_13_00.bak' WITH NORECOVERY RESTORE LOG your_database FROM DISK = 'log_14_00.bak' WITH STOPAT = '2015-11-19 13:29:59.000', RECOVERY
Using SSMS (SQL Server Management Studio)
- Right click on the database that should be restored from the list, select “Tasks” – “Restore” – “Database…”
- In the window that appeared press “Timeline” button to set up the time for database restoration
- Check in “Specific date and time” to set up the time for database restoration (13:29:59), press “OK” to save changes, and press “OK” button in the previous window:
- When the restoration process is completed, the following message will appear on the screen:
