11

We recently switched to the FULL recovery model, with full backups being done every weekend, and differentials being taken every day.

The problem is, the differential backups don't always seem to work. The error message recorded in the job history is

Executed as user: username. ... 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Started: 11:20:12 AM Progress: 2013-01-25 11:20:13.90 Source: {450389BA-54C2-4892-9CD0-0126CA9B0ED8} Executing query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp".: 100% complete End Progress Error: 2013-01-25 11:20:14.40 Code: 0xC002F210
Source: Back Up Database (Differential) Execute SQL Task
Description: Executing the query "BACKUP DATABASE [MyDatabase] TO DISK = N'E:\Database Backups\MyDatabase_backup_201301251120.diff' WITH DIFFERENTIAL , NOFORMAT, NOINIT, NAME = N'MyDatabase_backup_20130125112014', SKIP, REWIND, NOUNLOAD, STATS = 10 " failed with the following error: "Cannot perform a differential backup for database "MyDatabase", because a current database backup does not exist. Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option. BA... The package execution fa... The step failed.

The full backup job completes successfully every time, and I can view it in msdb.dbo.backupset using the query found here, so I know it exists. And it looks like after a reboot, the differential back up does complete successfully up until the next full backup.

Here's a look at the job history I have:

1/16 - Full Backup - Success
1/17 - Differential Backup - Success
1/18 - Differential Backup - Success
1/19 - Differential Backup - Success
1/20 - Full Backup - Success
1/21 - Differential Backup - Failure
1/21 - Differential Backup - Failure
1/22 - Full Backup - Success
1/22 - Differential Backup - Failure
1/22 - Reboot
1/23 - Differential Backup - Success
1/23 - Reboot
1/24 - Differential Backup - Success
1/25 - Full Backup - Success
1/25 - Differential Backup - Failure

The reboots were due to an unrelated error where @@SERVERNAME was null, so I held off posting this question until I found out if fixing the @@SERVERNAME fixed the problem, but it didn't and now I have no idea where to start.

What can cause SQL Server not to recognize a full backup for a database, and how can I fix it?

Also I'm not sure if this is related, but the differential backups seem rather large, and I am wondering if they're running the differential off the full database backup taken in the very beginning (on 1/16) instead of the most recent full backup. The full backups are about 260GB, and the differentials from 1/23-1/24 are at 30GB each.

The jobs were setup using the SQL Server Maintenance Plan Wizard, and I'm using SQL Server 2005

The [View T-SQL] button for the Maintenance Plan returns this code:

Full Backup:

BACKUP DATABASE [MyDatabase] 
TO DISK = N'E:\Database Backups\MyDatabase_backup_2013_01_25_140607_2543780.bak' 
WITH NOFORMAT, NOINIT, SKIP, REWIND, NOUNLOAD, STATS = 10, 
    NAME = N'MyDatabase_backup_2013_01_25_140607_2523778'

Differential:

BACKUP DATABASE [MyDatabase] 
TO DISK = N'E:\Database Backups\MyDatabase_backup_2013_01_25_140537_4073936.diff' 
WITH DIFFERENTIAL, NOFORMAT, NOINIT, SKIP, REWIND, NOUNLOAD,  STATS = 10, 
    NAME = N'MyDatabase_backup_2013_01_25_140537_4053934'

This is very closely related to another question I have, however it was suggested in chat that I post a new question instead of editing the other one due to the questions being different and the existing answers on the other question.

Improve this question
7
  • My first thought is that there was a SAN or VM snapshot that's causing this. However, that doesn't explain why on 1/22-1/23 the first diff after the full failed and the next succeeded with no other full. Do you mind double checking that because I would expect a new full to need to be taken to get things working again?
    – cfradenburg
    Jan 25, 2013 at 19:07
  • @cfradenburg I double-checked the job history, and that is correct. The full backup job succeeded on 1/22 at 12:30am. I ran the differential job manually the next morning at 8:26am and it failed. We rebooted the server that night, and the differential that next morning at 12:30am succeeded. I think our network admin has been doing some stuff lately with VM though. How would that affect it?
    – Rachel
    Jan 25, 2013 at 19:13
  • 1
    Can you try running the script here to see if there are any other backups happening outside of what you have scheduled: jasonbrimhall.info/2012/11/30/backup-history
    – cfradenburg
    Jan 25, 2013 at 19:15
  • 1
    According to this answer on SF, it looks like VSS may cause issues. Any SAN/VM backups that are occurring may cause problems.
    – swasheck
    Jan 25, 2013 at 19:17
  • 1
    Another SF question to review: serverfault.com/questions/147459/…
    – user507
    Jan 25, 2013 at 19:21

1 Answer 1

Reset to default
6

If something is taking a snapshot behind the scenes that breaks SQL differential backups causing a full to need to be taken before a new differential. You can either stop whatever is taking the snapshot or ensure you're taking a full backup afterwards before you try to do a differential. If you use Ola Hallengren's Maintenance Solution that can automatically change a diff to a full when it runs into this scenario.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.