Using point in time recovery in SQL Server

Sometimes, you might want to restore your SQL Server database at a specific point of time. I will guide you through to use point in time recovery in SQL Server.

1. Your database recovery model must be FULL or BULK-LOGGED.
2. You have enabled Transactions Log Backup’s.

You have a SQL Server database that is being used by many users, one user has deleted some records by mistake and you have to restore them back.Deletions has been done at 5.00 PM.

1. Restore the latest Full backup with no recovery option.
2. Now Restore your Transaction logs with no recovery option, except the last one transaction
log back.Also all your transaction backup must be in sequence at restoration time.
3. Restore Last Transaction Log File with recovery option and STOPAT=’Date, Time’. The following is a sample script:

Restore database testing from disk='<DRIVE_LETTER>:\<BACKUP_LOCATION>\<FULL_BACKUP_FILENAME>.bak’ with norecovery
Restore log testing from disk='<DRIVE_LETTER>:\<BACKUP_LOCATION>\<TRANSACTION_LOG_BACKUP_FILENAME1>.trn’ with norecovery
Restore log testing from disk='<DRIVE_LETTER>:\<BACKUP_LOCATION>\<TRANSACTION_LOG_BACKUP_FILENAME2>.trn’ with recovery, stopat=’Sep 4, 2012 7:53:00 PM’