Point in time restore

Point in Time Recovery

A point in time recovery is restoring a database to a specified date and time. A point in time recovery is a method to recover your database to any point in time since the last database backup.
Specify the date and time that you want the restore process to stop restoring.

Point in Time Restore Steps

1. There are three recovery models, point in time is possible only for the databases that are using full or bulk logged recovery model. Under the bulk-logged recovery model, if a log backup contains bulk-logged changes, point-in-time recovery is not possible to a point within that backup. The database must be recovered to the end of the transaction log backup.
2-Restore the last full database backup and, if any, the last differential database backup without recovering the database (RESTORE DATABASE database_name FROM backup_device WITH NORECOVERY).
3-Apply each transaction log backup in the same sequence in which they were created, specifying the time at which you intend to stop restoring log (RESTORE DATABASE database_name FROM WITH STOPAT=time, RECOVERY).
4-The basic syntax is:

RESTORE LOG database_name FROM WITH STOPAT =time, RECOVERY

T-SQL Code:

-- Restore the full database backup.
RESTORE DATABASE AdventureWorks
FROM disk='backup file location'
WITH NORECOVERY;
GO

-- Restore the required Log backup with STOPAT keyword.

RESTORE LOG AdventureWorks FROM disk='backup file location' WITH RECOVERY,STOPAT = 'Apr 15, 2005 12:00 AM';