April 9, 2009 at 9:52 am
Recently the automatically-scheduled differential backups of my local database have been failing (the full backups still work).
The message in the ERRORLOG is:
BACKUP failed to complete the command BACKUP DATABASE Mandates WITH DIFFERENTIAL. Check the backup application log for detailed messages.
What exactly do they mean by the "backup application log"? I checked the SQLAgent log, but didn't see anything that seemed to relate to this. Is there another log I should be looking at?
This problem seems to sporadically appear. For several months the differential backups were not working, and then they mysteriously started working again. I am not overly concerned about my local database, but nevertheless it would be nice to see it working.
I'm running developer edition, version 9.00.3310.00.
Thanks,
Cynthia
April 9, 2009 at 10:19 am
Did you check the Application Log of the Event Viewer?
Francis
April 9, 2009 at 10:25 am
Yes -- it says exactly the same thing, that I should check the backup application log.
April 9, 2009 at 10:38 am
Did you check the SQL Server Logs, not the SQL Server Agent Logs.
April 9, 2009 at 10:39 am
Are you using the Native SQL Server Backup or a third-party tool?
April 9, 2009 at 10:41 am
My backup jobs are all done via stored procs or T-SQL so the backup step would be something like:
BACKUP DATABASE.....
If you go to the advanced tab for the step I put in an output file which shows me the output from my command. If the Backup failed I so to this log and check out what it says. Perhaps they mean this file which may not exist unless you set it up. If you are doing your backups via a database maintenance plan I am sure there is a facility for creating error or logging output. I am not too familiar with this becasue I never use maintenance plans.
Francis
April 9, 2009 at 10:46 am
Yes, I did check the SQL Server logs -- that's where I first found the error. And yes, I am using the native backup tool, not a third-party tool.
FYI, here's a slice of the SQL server log from when it got the error:
***************
2009-04-07 22:00:27.89 Backup Database backed up. Database: Mandates, creation date(time): 2006/09/06(16:37:05), pages dumped: 1, first LSN: 997:9426:37, last LSN: 997:9442:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'{2ABFD362-C4CC-4652-86D5-4ECB00E821A2}11'}). This is an informational message only. No user action is required.
2009-04-08 00:00:36.32 spid23s This instance of SQL Server has been using a process ID of 1256 since 4/7/2009 8:53:21 AM (local) 4/7/2009 3:53:21 PM (UTC). This is an informational message only; no user action is required.
2009-04-08 01:00:01.84 Backup Error: 3041, Severity: 16, State: 1.
2009-04-08 01:00:01.84 Backup BACKUP failed to complete the command BACKUP DATABASE Mandates WITH DIFFERENTIAL. Check the backup application log for detailed messages.
***************
The first entry shows a full backup being done on the Mandates database at 10 PM, then some kind of informational message, then the attempt to do a differential backup at 1 AM, which fails.
Thanks,
Cynthia
April 9, 2009 at 10:58 am
fhanlon --
I use SQL Agent to set up a job to do full backups once a week, and daily differential backups once a day. However, in setting up the jobs, you have to supply a TSQL command. Here's the command I use to do a daily backup of the Mandates database:
BACKUP DATABASE Mandates
TO DISK='C:\SQL2005_Backups\AutoBackups\MandatesDiff.bak'
WITH DIFFERENTIAL
You are saying that I could add something to that to write to a log?
Thanks,
Cynthia
April 9, 2009 at 11:08 am
Cynthia DuBose (4/9/2009)
fhanlon --You are saying that I could add something to that to write to a log?
Absolutely. When you are editing this step notice on the left side of the window there is a GENERAL tab (where you are) and an Advanced tab. Click on the advanced tab and you will notice in the middle of the window it says Transact SQL Script and underneath Output file. Supply some file name here (it doesn't have to exist) . All output from yout BACKUP command will be written to this file. If you do not select Append output the file gets overwritten every time the job is written, which is likely a good thing, unless you want to add to this file via the next step of the job) The next step in my job is some code to delete old backup files.
HTH
Francis
April 9, 2009 at 11:32 am
Thanks, fhanlon, now we are getting somewhere! I added that to the job, and ran it, and here's the contents of the output file:
*********
Job 'MandatesDailyBack' : Step 1, 'Differential backup' : Began Executing 2009-04-09 10:24:40
Msg 3035, Sev 16, State 1, Line 1 : Cannot perform a differential backup for database "Mandates", because a current database backup does not exist. Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option. [SQLSTATE 42000]
Msg 3013, Sev 16, State 1, Line 1 : BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000]
*********
I'm finding this very strange, because, as my previous post showed, a full backup was done on this database at 10 pm on 4/7. For some reason it is not recognizing that. Any ideas???
Thanks,
Cynthia
April 9, 2009 at 12:46 pm
It sounds like the backup chain got broken. I assume this is a test box (since you said it was Developers Edition) and other people have access. If someone else took a full backup then the chain from last nights backup to logs to Differencial is broken, unless they used the WITH COPY_ONLY parameter on their backup. Another reason for the chanin breaking can be found at http://support.microsoft.com/kb/903643. I'll bet if you righ clicked on the database in EM and selected TASK, RESTORE the backup files listed don't match what you think is the latest Full backup.
Francis
April 9, 2009 at 12:50 pm
Can this database be restored by the users, or dropped and recreated by the users?
April 15, 2009 at 11:00 am
Francis --
It is a "test box" rather than the production server -- but it is in fact my personal computer running Windows XP, to which no one else has access (I am the sole IT person in this very small company) so no one else has been fiddling with the database.
The knowledge base link you included is interesting -- this is saying ntbackup can cause this problem. It looks like that article is specific to SQL Server 2000 and Windows Server 2003, but it is also true that my ntbackup consistently comes back with a message along the lines of "can't do shadow copy, switching to non-shadow copy mode". I looked into this once and it turns out it is caused by having a version of SQL Server on the machine that does not have a "simple recovery mode" -- see this article http://support.microsoft.com/kb/828481/en-us. (This article is from 2007 and there has been a service pack since then, and presumably the issue should be fixed -- yet I am still getting the backup message.)
Since I am not really concerned whether it does shadow copy or not, I never looked into it further. I wonder if the ntbackup is interfering in some way? It does an incremental backup every night and a full backup once a week. (I am only backing up a relatively few selected files, not the entire disk, and not the database files or the database backup files.)
"I'll bet if you righ clicked on the database in EM and selected TASK, RESTORE the backup files listed don't match what you think is the latest Full backup." -- in fact they do. The latest backup was 4/11, and that one shows up in the list.
UPDATE: I'm noticing that the last differential backup that worked was the day before I started doing automated daily incremental ntbackups of my pc ... hmmm ....
April 15, 2009 at 11:00 am
Lynn -- No, the only person with access is me.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply