Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favourite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved 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: 1407 | Views in the last 30 days: 4
 
Related Articles
SCRIPT

Backup & Restoration Script

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

SCRIPT

Script to Restore Entire database from a directory

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

ARTICLE

Powershell Database Backup Script

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

ARTICLE

T-SQL Restore Script Generator

A procedure that generates point in time restore database & log commands for your databases.

SCRIPT

Restore All Backups

SCRIPT TO RESTORE BACKUP FOR ALL DATABASES, PLACED IN FOLDER CALLED ORIGIN_FOLDER, WITH THE INITIAL...

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones