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.

diff backup will fail incase sql services has never taken a full backup! considering this point, can you check if you get these two database restored every night or get deleted and created fresh? or as an alternative add a new job steps to do a full backup (if are of small size) of these two db's before starting diff backup. Another, check the SQL Server logs for any database restore or creation logs over the night.

All databases have a full backup completing successfully (as well as log backups). The backups are verified and tested. There is no restore/creation activity going on. There's no benefit adding a full before the diff - I may as well just run the full and not bother with diff's!

Why not to check Application and System Log between midnight and 3am?

I had similar issue when third party backups were interfering with MS SQL backups.

Look at Logs; it possibly will give you a clue.

Tip: try not to overlap Full backups with third party backups.

Thanks for the response. I'm already certain that it is Bacula that is causing the issue. Its not a question of a timing overlap since I get the same problem when I adjust the schedules to ensure that they don't run at the same time. What I can't understand is why I only see a problem with the same 2 databases when all of the others in the same schedule are fine.

can you post last 48 hours sql server error logs for us to look at?

By any chance, is it using COPY only option in backup command.

What do you mean by system db freez/thaw? are you using SAN (snap/clone) backups?

 

No - it's a standard backup. The same plan is used for the DBs that work fine. The freeze/thaw items are in the server log and are generated by the VSS provider when it snapshots a database (via NT Backup or in this case Bacula). Only the system DBs and the two affected DBs are mentioned in the log which is why I'm confused. I know that the other DBs that get a diff fine are also captured by the Bacula backup as I've had restores of them in the past. I just can't understand why VSS is selecting these two DBs for a snapshot when they are in Full Recovery.

i do understand you have problem in system & its very much confusing.. but until you providing us relevant data I can't offer you help or diagnosing path. let us know if you ready to send sql server error log file?

Here's a relevant portion of the log - other than this it is simply a run of successful messages from the full, diff & log backups. You'll note the database Genesis is backed up by a VSS snapshot along with the system DBs in simple recovery mode. Genesis is in Full Recovery mode so as far as I'm aware should not be getting dealt with in this way as non of the other DBs in Full Recovery mode go through the I/O freeze/thaw which is where diff backup chain is clearly getting broken. 06/16/2009 02:41:02 Backup Unknown Database backed up. Database: msdb creation date(time): 2005/10/14(01:54:05) pages dumped: 1 first LSN: 599:136:99 last LSN: 599:192:1 number of dump devices: 1 device information: (FILE=1 TYPE=VIRTUAL_DEVICE: {'{43B6759F-8FBF-42DF-A2FF-F391CE68CB4D}3'}). This is an informational message only. No user action is required. 06/16/2009 02:41:02 Backup Unknown Database backed up. Database: Genesis creation date(time): 2009/02/27(18:40:41) pages dumped: 1 first LSN: 69:200:37 last LSN: 69:216:1 number of dump devices: 1 device information: (FILE=1 TYPE=VIRTUAL_DEVICE: {'{43B6759F-8FBF-42DF-A2FF-F391CE68CB4D}4'}). This is an informational message only. No user action is required. 06/16/2009 02:41:02 Backup Unknown Database backed up. Database: master creation date(time): 2008/07/03(23:31:12) pages dumped: 1 first LSN: 725:400:37 last LSN: 725:488:1 number of dump devices: 1 device information: (FILE=1 TYPE=VIRTUAL_DEVICE: {'{43B6759F-8FBF-42DF-A2FF-F391CE68CB4D}1'}). This is an informational message only. No user action is required. 06/16/2009 02:41:02 spid53 Unknown I/O was resumed on database model. No user action is required. 06/16/2009 02:41:02 spid59 Unknown I/O was resumed on database msdb. No user action is required. 06/16/2009 02:41:02 spid58 Unknown I/O was resumed on database Genesis. No user action is required. 06/16/2009 02:41:02 spid57 Unknown I/O was resumed on database master. No user action is required. 06/16/2009 02:41:01 spid57 Unknown I/O is frozen on database master. No user action is required. However if I/O is not resumed promptly you could cancel the backup. 06/16/2009 02:41:01 spid58 Unknown I/O is frozen on database Genesis. No user action is required. However if I/O is not resumed promptly you could cancel the backup. and here is the earlier successful full backup: 06/15/2009 22:00:29 Backup Unknown Database backed up. Database: Genesis creation date(time): 2009/02/27(18:40:41) pages dumped: 228 first LSN: 69:177:37 last LSN: 69:193:1 number of dump devices: 1 device information: (FILE=1 TYPE=DISK: {'D:\Backups\Daily Backups\Genesis\Genesis_backup_200906152200.bak'}). This is an informational message only. No user action is required.