Puzzling backup issue

Question

Puzzling backup issue

WayneS

SSC Guru

Points: 95461
More actions
October 6, 2010 at 11:20 am

#224816

I have two servers (with 3 instances each) on which we recently performed an in-place upgrade from SQL 2005 to SQL 2008.
Each instance has a maintenance plan that performs a full backup of user databases on Sundays, and differential backups Monday-Saturday.
The full backups on Sundays are working fine, as are the differential backups Monday and Tues.
However, starting on Wednesday, various instances will fail on the differential backups.
The Maintenance Plan report files show:
Failed:(-1073548784) Executing the query "BACKUP DATABASE [MyDB] TO DISK = N'D:\\Backups\\CC..." failed with the following error: "Cannot perform a differential backup for database "MyDB", because a current database backup does not exist. Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option.
This is acting as if the databases don't have a full backup, but they are there. I also checked the maintenance plans to ensure that the full backups aren't set to expire (they weren't).
Any ideas of what is causing this, or how to correct it?
Thanks,
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

winash SSChampion Points: 11446 More actions · Answer 1

Maybe this -> http://serverfault.com/questions/147459/cannot-perform-a-differential-backup-for-database-mydb-because-a-current-data

Adiga One Orange Chip Points: 27284 More actions · Answer 2

Is NTBackup scheduled to run on Wednesday? This can be a possibility.

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

Adiga One Orange Chip Points: 27284 More actions · Answer 3

or some job is switching the RECOVERY model to SIMPLE and back to FULL?

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 4

Adiga (10/6/2010)
or some job is switching the RECOVERY model to SIMPLE and back to FULL?

Differential backup can work with all recovery models, so I don't think that switching recovery model, should invalidate the backup. The only thing that I can think about is that the database was dropped and created (or reattached) since the last full backup. Can you check the database's creation date?

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

WayneS SSC Guru Points: 95461 More actions · Answer 5

Okay, I am seeing the IO frozen/thawed messages in the sql log, so it does appear that NT backup is the culprit. Time to work with the SAs to fix this.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2