Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite
2

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.

share|improve this question
2  
Was Server1 in full recovery mode when you took those two backups? Were you taking log backups on Server1 as well? – Aaron Bertrand Jan 17 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 Jan 17 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 Jan 17 at 18:49
3  
Then you cannot restore to a point in time, sorry. You either take backup1 or backup2. – Aaron Bertrand Jan 17 at 19:19

migrated from stackoverflow.com Jan 17 at 20:50

2 Answers

up vote 3 down vote accepted

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.

share|improve this answer
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 Jan 17 at 19:00
1  
No, the second full backup has the end result of all the transactions, not the transactions themselves. – DaveE Jan 17 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 Jan 17 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 Jan 17 at 19:20
1  
@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 Jan 17 at 19:51
show 2 more comments
up vote 1 down vote

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)
share|improve this answer
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 Jan 18 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 Jan 18 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 Jan 18 at 15:25

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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