Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

BACKUP LOG cannot be performed ... Expand / Collapse
Author
Message
Posted Tuesday, April 19, 2011 9:06 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, March 22, 2014 10:40 PM
Points: 44, Visits: 194
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!
Post #1096059
Posted Tuesday, April 19, 2011 11:36 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 6:05 AM
Points: 40,258, Visits: 36,681
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1096078
Posted Wednesday, April 20, 2011 11:23 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, March 22, 2014 10:40 PM
Points: 44, Visits: 194
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.
Post #1096531
Posted Friday, January 18, 2013 6:37 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, November 25, 2013 7:37 AM
Points: 14, Visits: 55
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
Post #1408878
Posted Friday, January 18, 2013 7:21 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 6:05 AM
Points: 40,258, Visits: 36,681
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1408906
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse