How To Use Point In Time Recovery In SQL Server

In this tutorial i will guide you through How To Use Point In Time Recovery In SQL Server.This is a very useful solutions.It will help you to restore your database at a specific point of time.But their are some limitations or you can say conditions for it.

1. Your database recovery model must be FULL or BULK-LOGGED.

2. You have enabled Transactions Log Backup’s.

Problem:

We have a 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.

Solutions:

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’.

Script:

Restore database testing from disk='G:\SQLDATA\Testing.bak' with norecovery
Go
Restore log testing from disk='G:\SQLDATA\1.trn' with norecovery
Go
Restore log testing from disk='G:\SQLDATA\2.trn' with recovery, stopat='Sep 4, 2012 7:53:00 PM'