Technical Article

Restore point in time

,

Usually before running any script even on DEV, I first make copy of the database and run it there first.

Today I didn't and I accidently dropped a table on DEV around 2:40pm on Dec 28th 2010. (Still in holiday mood :))

Luckily I had full backup taken at 2am on the same day.

Also, I had neither truncated log file nor shrunk database. (By the way, shrinking database is a bad idea. That's another topic.)

So this script came to my rescue. I am sharing with you. Hopefully it may help someone like me 🙂

I don't claim myself to be the expert. I am still a newbie in DBA world.

Please excuse me if I have made any mistakes. This is my first contribution to this site.

I thank you all for guiding me continuously through articles, blogs and scripts.

You need to have sysadmin permission to run this script.

Please do not run this directly on Production.

Paste this script in SSMS.

Be sure to kill all processes for the databases you are trying to restore point in time.

There are many scripts available online for this as well.

Since I couldn't locate the source of the script which kills all processes for a database, I am not including it here.

Make sure you have a valid full backup taken before running this script.

Also, make sure that database, you are tyring to restore, was not shrunk and/or log file was not truncated after taking full backup.

I have read few articles that say point in time restore may fail if database is shrinked or log file is truncated after taking the full backup. I may be wrong.

Replace <<YourDatabaseName>> with name of the database you are trying to restore point in time.

Make sure to you have enough space on drive to take backup. Tail of transaction log can be large.

In this example, I have used C drive. You may use any other accessible location including share drive.

This script has been tested on SQL Server 2005 only. It may work in SQL Server 2000 or 2008. I am not sure about it though.

Finally replace '<<Desired point in time>>' with valid point in time e.g. 'Dec 28, 2010 2:35 PM' .

 

 

 

/*Backup tail of transaction log without truncating it. It's very important here not to truncate transaction log.
Otherwise, you lose the ability to restore point in time*/BACKUP LOG <<YourDatabaseName>> TO DISK = N'C:\<<YourDatabaseName>>.trn' WITH NO_TRUNCATE , NOFORMAT, NOINIT, NAME = N'<<YourDatabaseName>>-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, NORECOVERY , STATS = 10, CHECKSUM
GO

/*Optional*/declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'<<YourDatabaseName>>' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'<<YourDatabaseName>>' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''<<YourDatabaseName>>'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM DISK = N'C:\<<YourDatabaseName>>.trn' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
GO

/*Restore last valid full backup. Make sure this file exist in C drive*/RESTORE DATABASE <<YourDatabaseName>> FROM DISK = N'C:\<<YourDatabaseName>>.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD, REPLACE, STATS = 10
GO

/*Restore tail of the transaction log till desired point in time*/RESTORE LOG <<YourDatabaseName>> FROM DISK = WITH FILE = 1, RECOVERY, STOPAT = '<<Desired point in time>>';--e.g. Dec 28, 2010 2:35 PM
GO

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

1.5 (2)

You rated this post out of 5. Change rating

Share

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

1.5 (2)

You rated this post out of 5. Change rating

Related content

External Article

SQL Server Database RESTORE WITH MOVE or not WITH MOVE

Mike Eastland frequently restores production databases to various development servers. The drive layouts between the servers are almost never the same. He is constantly shuffling files on his development servers due to space constraints, which in turn breaks the automated restore scripts because the MOVE clauses are invalidated. Does he have to use the MOVE clause every time he does a restore?

2013-12-05

4,001 reads