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

/*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 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

/*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


1.5 (2)

You rated this post out of 5. Change rating




1.5 (2)

You rated this post out of 5. Change rating