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.

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

Problem:
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.

Procedures:
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
Go
Restore log testing from disk='<DRIVE_LETTER>:\<BACKUP_LOCATION>\<TRANSACTION_LOG_BACKUP_FILENAME1>.trn’ with norecovery
Go
Restore log testing from disk='<DRIVE_LETTER>:\<BACKUP_LOCATION>\<TRANSACTION_LOG_BACKUP_FILENAME2>.trn’ with recovery, stopat=’Sep 4, 2012 7:53:00 PM’
Go

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: