April 19, 2011 at 9:06 pm
I am using SQL Server 2008 Express on a web server. Since SSIS and maintenance plans aren't available, I wrote a sproc, an sqlcmd file, and a bat file ... end result I have only one set of files and can pass parameter in. So I execute batch file from Task Scheduler passing in "Log" or "Database" or "Differential."
It works fine, quite nicely.
12.30 AM Full
3:30 AM Log
6:30 AM Diff
9:30 AM Log
12:30 PM Diff
3:30 PM Log
6:30 PM Diff
9:30 PM Log
Everything goes fine until the 9:30 PM Log backup, when for all User databases it failes with this in the audit log:
BACKUP LOG cannot be performed because there is no current database backup.
All the previous log backups succeed, and I'm just doing another Diff backup in between this Log backup at 9:30 pm and the previous one at 3:30 pm. What could be severing the sense of belonging to a media set or being aware of the Full backup taken at 12.30 AM?
What would I look at to find out?
Thanks for any ideas!
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 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply