0

I have two SQL Server database backups from Server1. Backup1 was made on, say, 2013-01-01 and Backup2 was made on 2013-01-03. What I want to do is restore Server2 to the intermediate state on 2013-01-02.

What I am trying to do is the following:

  1. Restore Backup2 onto Server2 so it has the latest data.
  2. Backup the transaction log on Server2.
  3. Restore Backup1 on Server2.
  4. Restore the transaction log using STOPAT = "2013-01-01 23:59:59"

I'm pretty sure I understand why this doesn't work. In step 2, I want the transaction log to go back to a certain backup, but there's no way to tell SQL Server which one I want. It just picks the latest one.

If that's correct, then I guess my question is, how can I trick SQL Server into giving me the transaction log I need. I've been looking at the backupset table and wondering if I can modify that to get what I need.

Server2 is a development server and not in production. I can modify it as necessary, even into an unusable state if it will help me get this data correct.

Improve this question
4
  • 2
    Was Server1 in full recovery mode when you took those two backups? Were you taking log backups on Server1 as well?
    – Aaron Bertrand
    Commented Jan 17, 2013 at 18:43
  • @AaronBertrand Server1 was in full recovery mode. So when I restore I get ldf files. We do not have transaction log backups, otherwise I would be able to use those.
    – Kris Harper
    Commented Jan 17, 2013 at 18:49
  • @RThomas If a mod thinks this would be better there, please move it. I don't want to double post. Although I guess I could delete this and repost there.
    – Kris Harper
    Commented Jan 17, 2013 at 18:49
  • 3
    Then you cannot restore to a point in time, sorry. You either take backup1 or backup2.
    – Aaron Bertrand
    Commented Jan 17, 2013 at 19:19

2 Answers 2

Reset to default
3

If you are in full recovery mode but NOT backing up transaction logs, then any state between two full backups is permanently lost. Sorry. You should either change your mode to basic recovery (since you are not making any use of your transaction logs) or you should do at least one transaction log backup before full backups.

Improve this answer
7
  • This is kind of what I thought, but I don't really understand why. The second full backup should have all of the transaction data, no?
    – Kris Harper
    Commented Jan 17, 2013 at 19:00
  • 1
    No, the second full backup has the end result of all the transactions, not the transactions themselves.
    – DaveE
    Commented Jan 17, 2013 at 19:06
  • @DaveE Got it. But then why is my ldf file so large? It's nearly as large as the mdf file, in the 10s of GB. I figured it must be holding the transaction data between backups.
    – Kris Harper
    Commented Jan 17, 2013 at 19:12
  • 2
    @KrisHarper your .ldf is large because you've never backed it up. You can't use the current .ldf directly to affect a restored copy of the backup - but if you take log backups, you can use those...
    – Aaron Bertrand
    Commented Jan 17, 2013 at 19:20
  • 2
    @KrisHarper the .ldf file is meant to assist the current database in logging operations and facilitating log backups, not meant to use directly against a restored copy. In a proper configuration when you are backing up the log, this lets SQL Server clear out the log activity that's in the .ldf file, so there should be no range from backup A to point in time B. In that way the .ldf file is meant to be a transient place for transaction data, not part of disaster recovery. Here's a starting point: msdn.microsoft.com/en-us/library/ms190440(v=sql.105).aspx But to be blunt: back up your log.
    – Aaron Bertrand
    Commented Jan 17, 2013 at 19:51
2

As your database is in FULL recovery model, it means that ALL transactions are stored in the online transaction log until a transaction log backup is created. The fact that you are not taking transaction log backups shows WHY the LDF file is huge. So, the transactions you want to roll back are still in there.

When you create a transaction log backup - the transactions are stored in the backup file and these same transactions in the online tlog are marked for overwriting. When you don't create transaction log backups - the transactions in the LDF file are never overwritten by new transactions and the LDF file grows, and grows and grows....

So - to make it short - when your database is in FULL recovery model and you have never created a transaction log backup, ALL transactions are still in your online transaction log

More details from Paul Randal here: Understanding Logging and Recovery in SQL Server

To rollback to a specific point back in time:

  1. Create MDF and LDF file copies
  2. Restore them on the test server
  3. Create a transaction log backup on the test server
  4. Follow the steps given here: Restore a SQL Server Database to a Point in Time (Full Recovery Model)
Improve this answer
3
  • This is how I understood the problem. The issue is that in step 3, my transaction log backup is really small, only about 400K. This can't possibly contain all the transaction data from a 10+ GB ldf file.
    – Kris Harper
    Commented Jan 18, 2013 at 13:27
  • #Kris Harper If you restored your database using full database backup "Restore Backup2 onto Server2 so it has the latest data.", that's expected, as database backups contain almost no transactions. The scenario I recommended uses original MDF and LDF file to create a database on the test server
    – Carol Baker West
    Commented Jan 18, 2013 at 14:22
  • Okay, this makes sense. Unfortunately I don't think it will work. After talking with my sysadmins, it seems that effectively transaction log backups were taken, but not stored. When doing a transaction log backup today, the resulting file is very small. This doesn't really make sense to me, because the LDF file is still somewhat large. But it seems to be the case that transaction logs are being cleared.
    – Kris Harper
    Commented Jan 18, 2013 at 15:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.