SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Restore point in time

By Parag Tengshe,

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




Total article views: 1477 | Views in the last 30 days: 2
Related Articles

Backup & Restoration Script

This script provides very useful information about database backup and restoration.


Generate RESTORE DATABASE Script without Using CMDSHELL

Use this script to generate RESTORE DATABASE scripts for all .BAK backup files from backup location ...


Script to Restore Entire database from a directory

Script to restore the entire databases from the backup directory just by passing the backup director...


Restore script for Ola Hallengren backup

Create a restore script based on backup files in a directory


Powershell Database Backup Script

Learn how to write a script for regular database backups using Powershell and SMO.