December 17, 2013 at 7:41 am
I have weekly full backups (Sunday at 1 am)
I have daily differential backups (other nights at 1 am)
I have daily transaction log backups (daily at 1 pm)
I have the transaction log itself.
So, on Tuesday night we determine that the database has a problem and we would like to recover to Tuesday at 3 pm.
If I restore the full backup, the Tuesday differential backup and the Tuesday transaction log backup, that gets me to 1 pm.
How do I replay the transaction log to get to 3 pm?
(Yes, I did google this - but it only showed me how to get back to a point before 1 pm).
Thanks!
December 17, 2013 at 7:51 am
Maybe you should brush up on your google skills:-D
December 17, 2013 at 8:02 am
Daily transaction log backups? That means that up to 24 hours data loss is acceptable, so restoring to 1PM should be fine.
If it's not fine, then that log backup frequency needs changing.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 17, 2013 at 8:05 am
The google articles I see show how to recover to a point in time using backups. I am asking about a case where the transaction logs exist.
I thought that one reason that I am keeping my transaction logs on separate LUN is so they will be available to help recover to (for example), the current time.
My question is - what is the process to do so? How do you use the transaction log file itself, as opposed to the transaction log backups?
Do I need to take an additional transaction log backup so that I can then use it, or is there a way to replay the log itself?
December 17, 2013 at 8:51 am
inevercheckthis2002 (12/17/2013)
Do I need to take an additional transaction log backup so that I can then use it, or is there a way to replay the log itself?
getting another log backup is the right thing to do. There may be a way to rebuild database directly from tlog but if even possible( not sure if it is) it would probably come with caveat of possible data loss.
so you have Sunday full, Tuesday 1am diff, and Tuesday 1pm tlog. Do you also have Wednesday 1pm tlog backup?
in short, you could restore Sunday full, restore Tuesday diff, restore Tuesday tlog, then restore Wednesday tlog up to Tuesday at 3pm.
December 17, 2013 at 9:00 am
As GilaMonster says, in order to be able to recover to a point in time, you will need to increase the frequency of your transaction log backups. In other words, I hope this is a theoretical question at the moment, as otherwise you're out of luck :-D.
What about backing up your log once an hour? Can you afford to lose up to an hour's data?
December 17, 2013 at 9:01 am
As pointed out by previous poster you need to have a tlog back that was taken after the time you want to restore to i.e. Wed 1pm Tlog backup. Make sure that you restore all backups with NORECOVERY and the last tlog backup (Wed 1pm) with Recovery and stopat = The day and time you want to stop the restore at.
The other issue is that you should make sure that you understand the transaction log and what it does. How to maintain it's size, # of VLFs, restore to a point in time, tail of the log backups, etc. I would suggest that you download this book and read so that you have a better understanding of the transaction log.
December 17, 2013 at 10:05 am
inevercheckthis2002 (12/17/2013)
The google articles I see show how to recover to a point in time using backups. I am asking about a case where the transaction logs exist.I thought that one reason that I am keeping my transaction logs on separate LUN is so they will be available to help recover to (for example), the current time.
My question is - what is the process to do so? How do you use the transaction log file itself, as opposed to the transaction log backups?
Do I need to take an additional transaction log backup so that I can then use it, or is there a way to replay the log itself?
The reason all the articles use backups is because that is how you do the recovery. You have to use the full backup to get the committed pages back, then you use the log backups as the mechanism that, to all intents and purposes, "replays" the log.
I wrote an article on it here on SQL Server Central[/url] quite a while ago. Kathi Kellenburger wrote one the same week[/url]
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply