SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

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

markle
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?

Cheers,
Mark

chadmat
The Chadinator

USA
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?

-Chad
Go to Top of Page
  Previous Topic Topic Next Topic  
 New 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.03 seconds. Powered By: Snitz Forums 2000