January 19, 2009 at 5:43 am
I suspect I'm screwed but would like to explain this problem in case I've missed something.
Setup is SQL2000, Full recovery model, complete backup every Saturday morning, transaction log backups weekday evenings (not ideal, but there it is).
I have working Full backups Saturday 10th and 17th, but the transaction log backups stopped after completing the backup on Tuesday 13th. I need to restore to a point in Friday 16th afternoon.
Not being a DBA or by any means a backup expert, from my understanding I believed I could restore the full backup from the 17th, backup the transaction log from that restore, restore the full backup from the 10th, then use the transaction log just created to PiT restore up to Friday? However, the transaction log backup (unsurprisingly) is tiny and presumably only contains transactions made since the restore (ie essentially nothing).
Given that no Transaction log backups took place since Tuesday, are the subsequent transactions "safe" inside the .ldf file of the Sat 17th Full backup and if so, how do I extract them? Or are they lost forever, removed by the full backup process?
I have tried researching the issue but seem to be going around in circles. If anyone could give me a definitive answer or point me to a clear guide/forum I'd be very grateful.
Thanks in advance.
January 19, 2009 at 6:06 am
what does it say if you execute
sp_dboption Database,'trunc. log on chkpt.'
You can verify LSN info using
Restore headeronly from disk='YourBakFiles'
January 19, 2009 at 7:19 am
andy.brown (1/19/2009)
I suspect I'm screwed but would like to explain this problem in case I've missed something.Setup is SQL2000, Full recovery model, complete backup every Saturday morning, transaction log backups weekday evenings (not ideal, but there it is).
I have working Full backups Saturday 10th and 17th, but the transaction log backups stopped after completing the backup on Tuesday 13th. I need to restore to a point in Friday 16th afternoon.
Not being a DBA or by any means a backup expert, from my understanding I believed I could restore the full backup from the 17th, backup the transaction log from that restore, restore the full backup from the 10th, then use the transaction log just created to PiT restore up to Friday? However, the transaction log backup (unsurprisingly) is tiny and presumably only contains transactions made since the restore (ie essentially nothing).
Given that no Transaction log backups took place since Tuesday, are the subsequent transactions "safe" inside the .ldf file of the Sat 17th Full backup and if so, how do I extract them? Or are they lost forever, removed by the full backup process?
I have tried researching the issue but seem to be going around in circles. If anyone could give me a definitive answer or point me to a clear guide/forum I'd be very grateful.
Thanks in advance.
Full Backups wont remove inactive transactions from ur .ldf file
if there is no tran log backup after tuesday, you shud manually fire tran log backup and then restore 10th Full backup, 11th, 12th tran log backup and manual tran log backup with stopat clause... The first three restorations (full and 2 tran logs ) shud be with norecovery clause. last one with recovery clause..
If ur tran log backup is small (as u reported), (this may be due to lesser number of transactions or someone have truncated the log without backing it up)... In the later case, i doubt the manual tran log backup will help u out...u'll lose transactions that happened after tuesday tran log backup and sunday full backup..
January 19, 2009 at 7:23 am
andy.brown (1/19/2009)
I have working Full backups Saturday 10th and 17th, but the transaction log backups stopped after completing the backup on Tuesday 13th. I need to restore to a point in Friday 16th afternoon.
Was the log truncated at any point after the tuesday log backup? (switch to simple recovery, backup log with truncate)
Is the database (or at least the log still accessible? If so and there was no trucnation, you can backup the log now, restore the full backup from the 10th and then this log backup with the STOP AT command.
If the database is not accessible, or there was any log truncation, then the best you're going to be able to do is restore the backup from the 10th.
However, the transaction log backup (unsurprisingly) is tiny and presumably only contains transactions made since the restore (ie essentially nothing).
That is correct. The restore of the full does not replace the tran log as it was. No need. Hence any log backup made afterwards will contain only the transactions after the restore.
Given that no Transaction log backups took place since Tuesday, are the subsequent transactions "safe" inside the .ldf file of the Sat 17th Full backup and if so, how do I extract them? Or are they lost forever, removed by the full backup process?
No. The full backup neither backup up the entire log nor does it truncate the log
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
January 19, 2009 at 8:02 am
GilaMonster (1/19/2009)
andy.brown (1/19/2009)
I have working Full backups Saturday 10th and 17th, but the transaction log backups stopped after completing the backup on Tuesday 13th. I need to restore to a point in Friday 16th afternoon.Was the log truncated at any point after the tuesday log backup? (switch to simple recovery, backup log with truncate)
Is the database (or at least the log still accessible? If so and there was no trucnation, you can backup the log now, restore the full backup from the 10th and then this log backup with the STOP AT command.
If the database is not accessible, or there was any log truncation, then the best you're going to be able to do is restore the backup from the 10th.
However, the transaction log backup (unsurprisingly) is tiny and presumably only contains transactions made since the restore (ie essentially nothing).
That is correct. The restore of the full does not replace the tran log as it was. No need. Hence any log backup made afterwards will contain only the transactions after the restore.
Given that no Transaction log backups took place since Tuesday, are the subsequent transactions "safe" inside the .ldf file of the Sat 17th Full backup and if so, how do I extract them? Or are they lost forever, removed by the full backup process?
No. The full backup neither backup up the entire log nor does it truncate the log
Many thanks for the clear and concise reply, this is exactly what I needed to clarify the ambiguity in my reading. Much appreciated.
As a matter of interest, can you recommend a website/article/or even book that explains clearly (ie for idiots like me) the structure & mechanisms of SQL Server transaction logs? There seems to be a lot of confusion about the virtual/physical/logical aspects and truncation etc. on the forums, I'd like to gain a reasonable grasp of their construct and concepts. There's plenty of how-tos, troubleshooting and commerce/software but I've struggled to find a good "how-it-works" explainatory resource.
January 19, 2009 at 8:27 am
Sure. Have you looked in the articles section of this website?
You can read through this - Managing Transaction Logs[/url] and this http://technet.microsoft.com/en-us/magazine/2009.02.logging.aspx
And if you're still unsure, ask.
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply