Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
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 '13 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 '13 at 16:50
    
That build of SQL Server is almost 6 years old. Just sayin'. –  Jon Seigel Jan 21 '13 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 '13 at 22:19
add comment

2 Answers

up vote 1 down vote accepted

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.

share|improve this answer
    
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 '13 at 19:33
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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