SQL 2005 differential backups failure

[johnhugh]

Hi all,

I'm running SQL Server 2005 SP2.
I've set up 2 maintenace plans. 1 for full backups to run every night. Another one for differential backups to run every 3h.

I back up 7 different databases. Full backups are working just fine and so do 5 of the differential backups.
The other 2 differential backups fail with this error message:

Error: 3041, Severity: 16, State: 1.
2010-04-09 09:15:04.25 Backup BACKUP failed to complete the command BACKUP DATABASE mydatabase WITH DIFFERENTIAL. Check the backup application log for detailed messages.

The application log doesn't contain any additional information.
Error 3041 means a differential backup won't run because it can't see a full backup.

Now the bizarre bit: If I run my full backup maintainance plan tonight, ALL my differential backups will work as well - up until another full backup run has been done.
After the 2nd full backup run, 2 of my 7 databases will fail - thinking there is no full backup.

If I try running the differential backup manually I get the following error message:

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.

I don't understand why it would work for some databases but not for others?
Any help much appreciated.

 

[Crowley16]

probably something you've checked already but just thought I'd mention it...

have you checked that your full backups for those 2 databases aren't being cleared down by something else? maybe a cleanup task?

--------------------
Procrastinate Now!

 

[johnhugh]

Thanks for your reply.
I've got a cleanup task but it's set to delete backups older then 1 day. I've set it to 2 days now just to try.
The actual files of my last full backup are where they should be. They don't get deleted.

 

[SQLBill]

Are these databases 'owned' by an application? For example, SharePoint application has its own database and SharePoint does its own backups.

If the two problem databases are created/controlled via an application, then the application might be making full backups and conflicting with your backups.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[johnhugh]

Hi Bill,

Thanks for your reply. All databases belong to the same application but the application doesn't 'own' them.

 

[johnhugh]

I have changed it now to backup transaction logs instead of differential backups for the 2 databases causing troubles.
Interestingly this seems to work even though no other setting was changed.

Now, can I restore a database with transaction logs the same way I restore a differential backup, i.e. restore full backup, then I think I have to restore all transaction logs since the last full backup and not just the last transaction log. Is that correct?