4

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

This worked last weekend, however this weekend the differential backup failed saying that

Message

Executed as user: username. ... 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Started: 12:30:00 AM
Progress: 2013-01-21 00:30:01.69
Source: {450389BA-54C2-4892-9CD0-0126CA9B0ED8} Executing query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp".: 100% complete
End Progress
Error: 2013-01-21 00:30:02.11 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_201301210030.bak' WITH DIFFERENTIAL , NOFORMAT, NOINIT, NAME = N'MyDatabase_backup_20130121003002', 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. BAC... The package execution fa... The step failed.

The job that runs the full database backup ran successfully, and I can view the backup using the query found here, so I am not sure why it is telling me "a current database backup does not exist".

I found this MSDN page which suggests that the cause of the problem is

This problem may occur when the internal status flag for a full database backup does not reset after you create the full database backup.

I'm not able to install the hotfix right away, and doing another full database backup is not an option right now, so I was looking to try manually resetting the internal status flag for a full database backup.

Is there a way to manually reset this flag so that differentials can successfully be ran for the rest of the week? Or to set the base backup to an existing full backup that exists in msdb.dbo.BackupSet/msdb.dbo.BackupMediaFamily?

I'm using SQL Server 2005

Improve this question
4
  • 1
    Can you clarify why doing another full database backup is not an option? Can you not support the I/O that the backup will use at this time (running the database during "production hours") or is there another reason?
    – Mike Fal
    Jan 21, 2013 at 16:09
  • @MikeFal Yes, a full backup to the local drive takes roughly 3-4 hours and starts causing performance problems for users using the database during this time. Backing up to a networked drive has better performance, however it takes 9-10 hours to finish instead. If I can't find a way to manually set the last full backup as the current base backup, then I'll be doing another full backup tonight during off-hours, although there is no guarantee that this will fix anything or that the same problem won't happen again tomorrow.
    – Rachel
    Jan 21, 2013 at 16:50
  • That build of SQL Server is almost 6 years old. Just sayin'.
    – Jon Seigel
    Jan 21, 2013 at 18:42
  • 1
    The age of the SQL Server version isn't really relevant. There are lots of people who are using much older versions that SQL 2005.
    – mrdenny
    Jan 22, 2013 at 22:19

2 Answers 2

Reset to default
1

I wouldn't reset that flag. If you did, which you probably can't, there's no guarantee that you'll be able to restore the diffs or log backups if you need to restore, and I can pretty much guarantee that changing that status flag is completely unsupported.

The only option here is to attempt to do a new full backup and see if the flag is reset.

Also that hotfix doesn't apply to you. According to the error you posted you are running 9.00.3042.00 while that hotfix is for below 9.0.1550.0 as it brings you up to 9.0.1550.0.

Improve this answer
1
  • I'm accepting this answer because I agree with you that it would be a bad idea to try to reset this flag manually. Another full backup did not fix the problem, however I posted a related question on here about the problem itself, and I think you guys have helped me find a solution to it :) (Also, thanks for the info about the hotfix)
    – Rachel
    Jan 25, 2013 at 19:33
1

Your primary goal here is to ensure your ability to restore the database. As long as you have the complete chain of your log backups from the last full to now, you can perform a point in time recovery and the error is merely an inconvenience. Differential backups are not required for full point in time recovery, just provide you with a shortcut for larger time frames. However, in the absence of your differentials, you'll need all the log backups to cover the gap.

Otherwise, the only way to properly reset this is to execute another actual full backup. I would recommend executing this as soon as possible which, depending on the size of your database, should be something that could be accomplished overnight in place of your normal differential.

Also, I would strongly recommend that you apply Service Pack 4 for SQL 2005 to make sure you have the most current hotfixes/updates for your platform.

Improve this answer

Your Answer

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

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