Can I manually reset the "internal status flag for a full database backup"?

All we need is an easy explanation of the problem, so here it is.

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

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 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.

Method 2

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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply