September 1, 2008 at 2:54 am
I have a bit of a strange issue where my backup maintenance plan for one of my databases fails on the Differential backup on the Sunday night.
What is strange is that I have a full backup that takes place on a Friday night, the differential backup works on the Saturday and then fails on the Sunday. I have no idea what is causing this and it has been happening for a few weeks now. When I run a manual full backup the maintenance plan Differential will start to behave again. I know there is a valid full backup as I can see it in the directory.
I have updated the server to SP2 and rebuilt the Maintenance plan and the issue is persisting. The server details and error messages are listed below.
If anyone has experienced similar issues or has any suggestions I would appreciate them, if any more information is needed please let me know and I'll dig it out.
Many thanks
John
Server Details
Edition: Standard Edition (64-bit)
Service Pack: SP2
Software Version: 9.00.3042.00
Maintenance Plan Details
Sub Plan 1: Weekly Full backup on Fridays at 18:00
Sub Plan 2: Daily Diff backup on Sat - Thurs at 18:00 (This one fails on the Sunday)
Package Error Message
Executed as user: APOLLO_FIRE\svc_SQL_%SERVERNAME%. ...042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Started: 18:00:00 Progress: 2008-08-31 18:00:00.80 Source: {BBD35348-C9D1-4E80-B3D2-0B6140C665C0}
Executing query "DECLARE @GUID UNIQUEIDENTIFIER
EXECUTE msdb..sp".: 100% complete End Progress Progress: 2008-08-31 18:00:00.92
Source: Back Up Database Task
Executing query "EXECUTE master.dbo.xp_create_subdir N'E:\DatabaseB".: 100% complete
End Progress Error: 2008-08-31 18:00:00.94 Code: 0xC002F210
Source: Back Up Database Task Execute SQL
Task Description: Executing the query "BACKUP DATABASE [%DatabaseName%]
TO DISK = N'E:\DatabaseBackups\%DatabaseName%\%DatabaseName%_backup_200808311800.bak'
WITH DIFFERENTIAL , NOFORMAT, NOINIT, NAME = N'%DatabaseName%_backup_20080831180000',
SKIP, REWIND, NOUNLOAD, STATS = 10 "
failed with the following error: "Cannot perform a differential backup for d... The package execution fa... The step failed.
Step Error Message
Executing the query "BACKUP DATABASE [%DatabaseName%]
TO DISK = N'E:\\DatabaseBackups\\%DatabaseName%\\%DatabaseName%_backup_200808311800.bak'
WITH DIFFERENTIAL , NOFORMAT, NOINIT, NAME = N'%DatabaseName%_backup_20080831180000',
SKIP, REWIND, NOUNLOAD, STATS = 10
" failed with the following error: "Cannot perform a differential backup for database "%DatabaseName%",
because a current database backup does not exist. Perform a full database backup by reissuing BACKUP DATABASE,
omitting the WITH DIFFERENTIAL option.
BACKUP DATABASE is terminating abnormally.".
Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly,
or connection not established correctly.
September 1, 2008 at 3:06 am
Are you doing anything strange to the DB between the saturday diff and the sunday diff?
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
September 1, 2008 at 3:13 am
For your reference: "The Ntbackup.exe operation may break the differential backup chain of a SQL Server database and may invalidate the differential backups when you perform a snapshot backup of the database "
http://support.microsoft.com/kb/903643
You should review the configuration of the Volume Shadow Copy with the windows administrators to insure that SQL Server databases are not included in the VSC backup plan.
SQL = Scarcely Qualifies as a Language
December 29, 2008 at 11:46 am
Hello everyone,
I'm having the same issue with my DIFF backup job....it fails everyday even if there is a backup file present. I took this issue with HP and they couldn't help at all after trying to reschedule the backup job, using T-SQL (instead of the Maintenance Plan), and it still failed. They're escalating the issue to Microsoft support. This is the weirdest problem i've ever faced.
I have a maintnenace plan that performance a full backup on a Sunday and DIFF backup on subsequent days. The DIFF backup fails with the following error:
"Cannot perform a differential backup for database "DB_Name", because a current database backup does not exist. Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option. "
Very odd, i'm left with Weekly fulls and daily Transaction log backups at the moment.
Any ideas on a solution?
Bini
December 29, 2008 at 11:55 am
Changing the recovery model from full to simple will invalidate the backup chain - causing differential backups to fail with the error you are getting.
This could also be caused (I believe) by issuing a BACKUP LOG ... WITH TRUNCATE_ONLY.
I would review all code that is running, focusing on maintenance processes like index rebuilds, archive/purge processes, etc... and see if one of those processes is doing one of the above.
You could also start a server side trace to see if you can capture the event when it happens.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 29, 2008 at 11:59 am
Cool....i started my traces on my production servers prior to your reply. But thank you for the reply, i will update you as soon as i can gather some data.
Thank you,
Bini
December 29, 2008 at 12:09 pm
Jeffrey Williams (12/29/2008)
Changing the recovery model from full to simple will invalidate the backup chain - causing differential backups to fail with the error you are getting.This could also be caused (I believe) by issuing a BACKUP LOG ... WITH TRUNCATE_ONLY.
Those two just break the log chain, causing any future log backups to fail. The diffs don't depend on LSN and are unaffected by recovery model changes.
There's a few odd things that break the diff base, not many though. I seem to recall there's a SQL bug that does it, but I don't know the context or version. I know a couple 3rd party VSS backup tools do as well
Bini: What are you using to do the backups? SQL native? What, if anything, do you use for file-system backups.
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
December 29, 2008 at 12:15 pm
I'm using Maintenance Plans for my backup strategy....i really don't like them (but that's for another episode) :-)....
I've used T-SQL to execute a DIFF command but no luck.
But as far as File-System backups go, my SA here uses Legato which is different from NTBackup.
December 29, 2008 at 12:26 pm
biniyb (12/29/2008)
But as far as File-System backups go, my SA here uses Legato which is different from NTBackup.
Can you check and ensure that Legato's not also doing SQL backups of any form? From what google tells me, it has a SQL backup component.
Check your backup history in MSDB, make sure that all the backups that are done are ones that you're doing.
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
December 29, 2008 at 1:04 pm
From what my SA tells me, it doesn't. Sorry for the late reply
December 29, 2008 at 1:30 pm
I'd run a trace of some sort, looking for the backup event in between the time when you run a full and the time a diff is scheduled. There must be something strange going on.
December 29, 2008 at 1:33 pm
Very strange things are occuring...i'm currently running a trace hoping i can find any kind of concrete evidence. Thank you
Bini
December 29, 2008 at 3:12 pm
GilaMonster (12/29/2008)
Jeffrey Williams (12/29/2008)
Changing the recovery model from full to simple will invalidate the backup chain - causing differential backups to fail with the error you are getting.This could also be caused (I believe) by issuing a BACKUP LOG ... WITH TRUNCATE_ONLY.
Those two just break the log chain, causing any future log backups to fail. The diffs don't depend on LSN and are unaffected by recovery model changes.
There's a few odd things that break the diff base, not many though. I seem to recall there's a SQL bug that does it, but I don't know the context or version. I know a couple 3rd party VSS backup tools do as well
Bini: What are you using to do the backups? SQL native? What, if anything, do you use for file-system backups.
With all due respect Gail - I am going to have to disagree. When you change the recovery model from SIMPLE to FULL or FULL to SIMPLE, the database is marked as *requiring* a full backup before you can perform a transaction log backup. This also applies to a differential backup...
In order for you to be able to perform a differential backup, you must have the base differential backup, which is a conventional backup, partial backup or file backup for that database. Because the database is marked as no longer having a current backup - you need to create a new base for the transaction log and differential backups.
Differentials also rely upon the LSN - which is stored in the column 'differential_base_lsn'.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 29, 2008 at 3:58 pm
I found out that the chain was being broken by out Sys-Admins taking system snapshots with BESR by symatec even though it was not activly backing up the databases with the SQL agents.
Have you got anything like that running in your environment?
December 29, 2008 at 4:01 pm
My SA uses Legato....When i had HP support on the phone we tried a full backup before the Legato backup and then a full backup after the Legato.....And the DIFF backup job still failed with the error. It's so weird...this is by far the weirdest i've ever run into in my young DBA career.
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply