Home / server / Questions / 26379
Accepted
Chris W
Asked: 2009-06-17 00:14:10 +0800 CST

SQL 2005 Diff backups failing - no full backup exists?

  • 772

I have a SQL 2005 SP 2 server running a maintenance plan that performs full backups every day at 22:00, diff backups every 4 hours and log backups every 30 minutes. The plan backs up all system & user databases (all user DBs are in Full recovery mode). The full & log backups all complete and verify without problem - i've confirmed this by doing some test restores. Every morning the diff backup throws an error from 2 databases stating that no full backup exists.

It's always the same two databases that have this error - all others are ok. If I manually run a full backup again the diff's will run fine until the next morning.

I'm fairly certain that the problem is down to a VSS backup that is run at midnight by the company that manage the servers using Bacula. In the SQL logs I can see that VSS is freezing/thawing the system DBs and these 2 databases only when Bacula runs.

Obviously I can just get the hosting Co to stop backing up the DBs within their job since they're picking up my SQL backup files anyway but I simply want to know why this is happening. What could be different about these 2 databases as every option i've checked is identical to the ones that complete fine. I have the same plan set-up on another server which runs with no problems.

Thanks, Chris.

sql-server sql-server-2005 backup

3 Answers

  1. I think that you should check out the job history for the job that executes the full backup at 22:00. There could be a warning and some database backups could be skipped, either that or the 2 database for which there's no full backup have no full backup scheduled. Also check the duration of the full backup job, there might be a problem indeed if Co's Bacula backup starts before the full backup completes (although 2 hours for a full backup of the databases is a little extreme). Anyways the message is explicit and for some reason the full backup does not occur on those 2 databases.

    • 1

  2. Are ALL your database running in full recovery mode? the VSS backups may behave differently depending on if your in full or simple recovery.

    If the VSS backup is happening after you do your diff backup and before you've run your full backup then this is your problem. You need the last full backup for your diff to be able to work.

    Your options as i see it are:

    1. Disable the SQL VSS Writer Service, to stop 3rd party backups that are breaking your backup chain
    2. Remove the built-in administrators group from the SQL Server sysadmin server role (just make sure you have another user in there, or you have the sa password) Which will effectivley break the SQL VSS service's ability to access SQL to perform these backups
    3. Ignore it & start using the log backups you have instead, just make sure you retain enough to cover you from YOUR last full backup.
    • 0
  3. Best Answer

    I've not reached a definitive answer as to why this happens on these two databases only (and only on this server) but I've settled for re-jigging the backup times on everything so that our full backup (SQL) happens immediately after the Bacula backup runs so we can reset whatever flag is getting set incorrectly. It's been running this way for a few days with no problems.

    • 0