Point in Time Database Recovery in SQL Server Database

Point in Time Recovery option used restore your SQL database without losing a single record to a specific date & time. Before using this option, there are a few things that need to be done. 

1) Database must be either in FULL RECOVERY MODEL or Bulk-Logged recovery model:

• You can easily check current status of recovery model of your database by using following command:
  
  

SELECT name,recovery_model_desc FROM sys.databases

• If your database is in SIMPLE RECOVERY MODEL then you can change it by using following T_SQL command:

ALTER DATABASE databaseName SET RECOVERY BULK_LOGGED

ALTER DATABASE databaseName SET RECOVERY FULL

2) If you are using the Bulk-Logged recovery model, you must ensure that there should not be any bulk logged operations in the transaction log that you are trying to restore. In this case, you can not do Point in Time Restore.

3) If you exactly knows the time to restore the transaction log. There are the commands to restore the database and one transaction log to June 23, 2012 08:35:00 PM. The first full backup restore is done using the NORECOVERY option. In this case the database stays in a loading state and additional backups can be restored. The second restore command restores the transaction log to the point in time = 'June 23, 2012 08:35:00 PM'. In addition, we are using the RECOVERY option to put the database back into a working state after the restore of the transaction log..

RESTORE DATABASE DBadam

FROM DISK = 'C:\Backup\dbadam.bak'

WITH NORECOVERY

RESTORE LOG DBadam

FROM DISK = 'C:\Backup\dbadam_log.trn'

WITH RECOVERY,

STOPAT = 'June 23, 2012 08:35:00 PM'

3) In some cases, you don't know the exact time something went wrong. In this case, transaction log reader tools will allow you to see all of the transactions and the time they occurred. So with the help of this tool, you can find out the exact time to recover your database.