8

I have what seems to be a pretty common problem when trying to take a differential backup. We have a SQL Server 2008 Standard (64bit) and we use Litespeed v 5.0.2.0 to take our backups. We take full backups once a week and a differential on a daily basis. The problem is, every time I try to take a diff backup I get the following error:

VDI open failed due to requested abort
BACKUP DATABASE is terminating abnormally.

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.

The problem is that I know 100% I have a full backup because I just double checked. Only once I was able to take a diff backup and that was when I took it immediately after I took a full backup.

I have searched around and noticed that this is pretty common (although mostly with SQL 2005) and a solution that a lot of ppl suggest and that I haven't tried yet is to disable the SQL Server VSS Writer service. The problem with this is #1 I think I might need this service since I am using a third party backup software and #2 I am not sure exactly what the service does and don't want to disable it just like that.

Has any of you ever experienced this problem and how did you go about fixing it?

Thank you,

MattB
  • 11,124
  • 1
  • 29
  • 36
user44127
  • 131
  • 1
  • 4

4 Answers4

7

Are you running VSS backups on the server (NTbackup, BE, etc.) for stuff other than the SQL data? According to this, the SQL differential backups can be invalidated if a backup software uses VSS for snapshots.

As far as I know, LS does not use VSS just like Nick said. According to this thread the fix was to disable the SQL Writer Service.

Update based on comments:

Because you are using NTbackup to backup other files on the server, the fix would be to disable the SQL Writer Service.

When NTbackup runs it calls ALL the available VSS writers, even if you don't need them. This means that the SQL writer will put the DB into a consistent state (flush cache to disk) while the snapshot is created. The DB then thinks it has been backed up, even if you didn't copy the information from the shadow copy. This is what's breaking your backups.

One thing you can check to see if VSS is running against the DBs (which I'm positive it is and breaking the chain), is look in the SQL log (I think its in the error log). You should see freeze/thaw events during the time your NTbackup jobs run.

Holocryptic
  • 5,665
  • 2
  • 28
  • 37
  • There is a couple of scheduled tasks that use ntbackup.exe to backup certain folders on the server. Wouldn't disabling SQL VSS writer mess up these ntbackup tasks? Thanks... – user44127 Jun 02 '10 at 15:57
  • @krimerd, no it wouldn't harm the backups. Since you are using LS to do the SQL backups, you should exclude the directories containing the DBs anyway. When NTbackup runs, it calls all the VSS writers available, whether or not you are going to use them. Disabling the SQL writer won't hurt you in this case. – Holocryptic Jun 02 '10 at 16:04
  • I might give this a shot but first I'd like to make sure that this is what's causing the error. I'll disable the ntbackup tasks, take a full sql backup and see if I get any errors trying to take a diff backups. If I dont get the error it's pretty safe to say that the ntbackup caused the issue. Thanks Holocryptic. – user44127 Jun 02 '10 at 16:25
  • @krimerd, no problem. One thing you can check to see if VSS is running against the DBs (which I'm positive it is and breaking the chain), is look in the SQL log (I think its in the error log). You should see freeze/thaw events during the time your NTbackup jobs run. – Holocryptic Jun 02 '10 at 16:44
  • Holocryptic, you're right...I just checked the SQL logs and at the time when ntbackup task is scheduled to run I can see several logs (one for each database) and all of them having 'Backup' as source and 'type = virtual_device". There are no backup jobs scheduled at those times and no one took any backups manually at those times so the only other thing left is the ntbackup task. This is little strange because the ntbackup that is scheduled @ that time only backups one folder and I don't see why it would touch any databases or sql server in general...thanks again – user44127 Jun 02 '10 at 17:55
  • @krimerd, like I mentioned earlier, when NTbackup runs it calls *ALL* the available VSS writers, even if you don't need them. This means that the SQL writer will put the DB into a consistent state (flush cache to disk) while the snapshot is created. The DB then thinks it has been backed up, even if you didn't copy the information from the shadow copy. This is what's breaking your backups. – Holocryptic Jun 02 '10 at 18:11
1

it's been a few years since i used lightspeed so this may have changed, but doesn't light speed provide its own extended stored procedures to performs backups? in which case, you should be calling these to do a differential. You probably shouldn't be calling the native BACKUP DATABASE commands. I don't think you can mix & match native/lightspeed backups.

I tried to poke around Quests online product doco, but alas you need to be a customer.

From SQL Server's perspective of native backups if you've never taken a full backup then you can't take a differential.

The VSS service allows for file level backups of sql server via volume shadow copy. If you're using lightspeed for backups & excluding the sql server directory from server level (OS) backups it's probably ok to turn this off. This is probably got nothing to do with not being able to take differential backups.

Does restarting the sql server instance fix the issue? if so, you may want to look at increasing sql servers memtoleave check this out.

Nick Kavadias
  • 10,758
  • 7
  • 36
  • 47
  • Thanks for your answer Nick. You're right, LS does provide its own storeprocs and I call these from my maintenance SSIS package. However, I still get the above mentioned error. Thanks again... – user44127 Jun 02 '10 at 14:11
  • looks like a quest specific issue! – Nick Kavadias Jun 02 '10 at 14:15
  • Yeah, it might but all the other people are getting this same error by doing the native backups. Thanks again – user44127 Jun 02 '10 at 14:36
1

The most common cause for this issue ocurring is if you have another backup software (NTBackup, BE, Bacula etc. ) which takes a snapshot (VSS copies – Volume Shadow Copy) of your database, then this can cause the chain of backups to be broken and invalidate your previous DIFFERENTIAL backups, which is why when your current DIFFERENTIAL backup process starts, it will fail as it can’t see that a initial FULL backup or another DIFFERENTIAL backup has occurred before it.

First of all, you need to find out which software tool is creating snapshot backups of your database because there are several ways how you can solve this issue. Let's consider the most common case when snapshot backups are making with the help of NTBackup. The Solution for NTBackup Primarily, check SQL Server VSS Writer in the Service menu it has to have the following settings: Start Type: Manual and the service has to be stopped. If SQL Server VSS Writer service is running and a snapshot is taken, the full recovery model will be blocked and the backup chain will be broken. To change SQL Server VSS Writer settings go to the "Star Menu" and find "Services" in the opened window select "SQL Server VSS Writer".

Right-click the service and select "Properties", in the opened window set the "Startup Type" to "Manual", press "Stop" at the "Service status" section to make sure that service will not run automatically after you restart your computer and click "OK" to apply all changes.

Olek Nilson
  • 111
  • 3
0

I had the same issue albeit with Bacula taking snapshots of the database as well as my own SQL Server backups. There's some kind of bug that was making this happen on a subset of the DBs on the server which was most annoying. In the end my resolution was to change backup timings so that a SQL Backup ran after the Bacula backup to ensure that SQL Servers internal flags got correctly reset to note that a full backup had been taken. That was on 2005 - I guess the same VDI problems are affecting 2008 as well.

For ref here was my earlier question.

Community
  • 1
Chris W
  • 2,670
  • 1
  • 23
  • 32
  • Yeah, it looks like the same situation I'm in. The only difference is that my databases are in the simple recovery mode (since I don't need to take tlog backups) and that in my case all 4 databases that I'm trying to backup fail with the same error. If nothing else, I'll try to stop the VSS writer and see if that helps and hoepfully that won't cause some other problems. Thanks Chris – user44127 Jun 02 '10 at 14:57