Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Can't seem to limit point in time restoration
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

1 Posts

Posted - 10/01/2012 :  12:38:43  Show Profile  Reply with Quote
Hi all, thank you for all the help so far learning to use SQL Server. I have searched for an answer on the following procedure but not found enough description. The devil seems to be in the details so I need to ask for your help:

I have an sql2008 server that I would like to recover data for.

The DB is in full recovery mode and I have a full database backup from Monday and full log back up from Monday at the same time. The Tuesday backup failed to run at all (no DB, no log) and I need to roll back to Tuesday afternoon. I have the working .ldf file for the database up to Wednesday afternoon. (There were some data changes Wednesday morning, which when discovered resulted in the server being taken offline).

I have tried backing up the log with NO_TRUNCATE to a .trn file and then restoring the Monday backup of the DB, the Monday log and then the above .trn to a point in time (Tuesday 1pm) but every time I end up with the Wednesday afternoon data (no errors during restoring).

I am using the SQL Management Studio (as I'm a very green beginner) and first do Tasks->Restore->Database using "WITH REPLACE" and "RESTORE WITH NORECOVERY". My back up source is the DB in question (not a device/file) and I am writing to an empty DB with another name that I alter in the "Restore the database files as" grid. At this point I am restoring to the most recent time (Monday Night).

I then do Tasks->Restore->Transaction Log and select the Tail-end log that I produced earlier (from file or tape) and specify a time earlier than when the data was changed. In options I choose RESTORE WITH RECOVERY and begin. after 100% completion / success, the STOPAT is still ignored. I have tried choosing Restore->Database rather than Transaction Log in that last step above but with the same result.

What am I doing wrong?


The Chadinator

1974 Posts

Posted - 10/01/2012 :  13:56:18  Show Profile  Visit chadmat's Homepage  Reply with Quote
Can you script it out and post the T-SQl?

Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000