3

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.

Improve this question

3 Answers 3

Reset to default
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.

Improve this answer
3
  • The backup definitely does occur for all database - as I've noted above I've completed test restores on my scheduled backups as well as the VSS ones to prove that all complete correctly. There's also no overlap on the backups as SQL full backups complete in a few minutes, some 2/3 hours before Bacula starts up.
    – Chris W
    Jun 16, 2009 at 8:37
  • i found the same problem posted here : sqlservercentral.com/Forums/Topic561898-357-1.aspx From what i've noticed there's one answer going like this : For your reference: "The Ntbackup.exe operation may break the differential backup chain of a SQL Server database and may invalidate the differential backups when you perform a snapshot backup of the database " support.microsoft.com/kb/903643 You should review the configuration of the Volume Shadow Copy with the windows administrators to insure that SQL Server databases are not included in the VSC backup plan.
    – Paul
    Jun 16, 2009 at 12:20
  • 1
    Thanks - i've seen those items. I presume Bacula is having the same issue that Ntbackup is having but I don't understand what is different about these two databases which causes them to be affected when all of the others aren't.
    – Chris W
    Jun 17, 2009 at 10:14
0

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.
Improve this answer
1
  • All databases are in full recovery mode. It's only 2 DBs from about 10 (plus another 10 DBs on another server) that are having this problem which is why I'm confused. I'm happy to stop VSS hitting these databases but I just want to know why these 2 only are showing a problem.
    – Chris W
    Jun 17, 2009 at 10:12
0

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.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .