April 19, 2011 at 11:36 pm
Between the diff and the failed log someone either ran BACKUP LOG ... TRUNCATE_ONLY or switched the DBs to simple recovery and back to full, thus breaking the log chain.
You'll need another full or diff before you can take log backups again, and you need to identify where (or who) that command was run from and stop it.
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
April 20, 2011 at 11:23 am
Yes, the helpful hosts had some routine scheduled that was supposed to be of assistance in some way.
I have asked them to desist.
Thank you for your response, Gail.
January 18, 2013 at 6:37 am
Hi Gail, googled right to your post, "hmm, yup, that's what I did." I was hoping it would be a good idea to do the full backup, then immediately after set SINGLE USER, SIMPLE RECOVERY, rebuild indexes, update statistics, then back to MULTI_USER, FULL RECOVERY (after all, why bother logging all that stuff?).
Is there a better way? (or should I be talked out of this idea?) thx
January 18, 2013 at 7:21 am
bwperrin (1/18/2013)
I was hoping it would be a good idea to do the full backup, then immediately after set SINGLE USER, SIMPLE RECOVERY, rebuild indexes, update statistics, then back to MULTI_USER, FULL RECOVERY
No. It's quite a bad idea. You're breaking the log chain, you won't be able to take log backups until you do another full or diff backup. You've removed the ability to restore across that time period using log backups.
Let's say that the full backup you take after that is damaged upon write (but you don't notice that) and you need to restore to a point a couple hours after that backup. You go to restore that full, but it's damaged and the restore fails. You go to restore the previous one, that restores but because of the broken log chain you can't restore to the time you need, so you've lost a couple hours of data because of switching to simple recovery.
(after all, why bother logging all that stuff?).
It's still logged in simple recovery.
If you want to minimise log usage, you can switch to bulk-logged recovery before the rebuilds and back afterwards, providing the risks of bulk-logged are acceptable. Otherwise, rebuild just what needs rebuilding and not everything.
P.s. New questions in a new thread in future please.
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 4 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply