BACKUP LOG cannot be performed ...

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » Backups » BACKUP LOG cannot be performed ...

BACKUP LOG cannot be performed ...

Rate Topic

Display Mode

Topic Options

Author

Message

Michael Gerholdt

Posted Tuesday, April 19, 2011 9:06 PM

SSC Rookie

Group: General Forum Members
Last Login: Friday, March 29, 2013 6:32 PM
Points: 43, Visits: 184

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

GilaMonster

Posted Tuesday, April 19, 2011 11:36 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:13 PM
Points: 37,729, Visits: 29,991

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

Michael Gerholdt

Posted Wednesday, April 20, 2011 11:23 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, March 29, 2013 6:32 PM
Points: 43, Visits: 184

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

bwperrin

Posted Friday, January 18, 2013 6:37 AM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, May 01, 2013 1:47 PM
Points: 14, Visits: 46

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

GilaMonster

Posted Friday, January 18, 2013 7:21 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:13 PM
Points: 37,729, Visits: 29,991

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 »

Permissions