THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Tibor Karaszi

Does your 3:rd party backup software ruin your SQL Server backup plan?

Sounds scary, huh? And it surely can be! Here’s an example of one such case:


A client of mine had implemented a 3:rd party backup solution in their VM Ware environment. This shouldn't affect the SQL Server backups. Or that was the plan. I won’t mention what backup software this is, since I’m sure that there are other software also doing strange things. And in the end, it is up to you to verify that your backup and restore strategy is solid. So let us just call this software “BACK”.


BACK performs a snapshot backup of the virtual machine. The client had scheduled this so it happened at about 4 am each day.


Our SQL Server backups were implements so that 7 pm we did a full backup and every hour we did a log backup.


Nothing strange here and these should be independent of each other, right? Wrong!


When looking at the backup history, I realized that SQL Server would see the snapshot backup as a full backup. OK, that will possibly affect out plan to go for differential backups, but it shouldn't affect our transaction log backups. We all know that a database backup doesn't break or affect the log backup chain.


But what I did find was that BACKUP performed a log backup immediately after the snapshot database backup. And the log backup was taken to the file name “nul”. Yes, the binary wasteland.


The end result of this was that the log backups that we performed were usable between 7 pm and 4 am. After that, the log backups are useless. No option to restore anything for the work performed during the working day. It would have been more honest if BACK would set the recovery model to simple instead. That would at least give us a more fair chance to catch the problem (sooner then we did).


Now, we did find an option in BACK to not truncate the log (or whatever they called the checkbox), but by default it did perform this disastrous log backup to nul.


The next step was to consider implementation of the differential backup plan. But time was running out for my assignment so we only managed a quick check. And from what we found out, it seemed that BACK doesn't have an option to produce its snapshot backup to be seen as a COPY_ONLY backup. This means that it prohibits us to implements SQL Server differential backup and saving some 500 GB backup storage produced each week (for only one of the servers). Now, let me leave a disclaimer here since I didn't have time to investigate this much as all, but this is what it looks like at the moment. I will dig deeper into the differential backup strategy next time I’m with this client.


The moral of the story? You already know that. Test your restore strategy. You might just be (unpleasantly) surprised!

Published Wednesday, April 9, 2014 12:04 AM by TiborKaraszi
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Greg Linwood said:

Use Log Shipping & your log backup / restore gets tested all day every day

April 8, 2014 9:49 PM

Jon Reade said:

Another great example of why databases should be left to DBAs, not developers who don't understand what they're doing. That's almost bordering on criminal negligence, given the nature of the "BACK" product.

April 9, 2014 5:49 AM

Dave Wentzel said:

Does anyone have any idea what may be the use case for this?  Backing up to nul after a snapshot?  

There are good use cases for backing up to nul...we do it on our non-prod envs so we can ensure we have all of our databases in FULL reco mode yet don't have to worry about log backup paths being different in different hardware setups.  We have a standard set of backup jobs and we flip a flag to do the backups to nul.  

But why would you want to take a snapshot and then backup the logs to nul?  Having a hard time wrapping my head around that one.  

April 9, 2014 8:51 AM

TiborKaraszi said:

One can only speculate, Dave.

I agree that there can be valid reasons, like for instance you have mirroring *and don't want log backups*. Etc.

My guess is that the backup vendor has heard of problems with large ldf files and thought it is a good service to have that option to truncate the log after the backup was performed.

April 9, 2014 10:12 AM

RichB said:

@Greg - sure the log chain gets tested, but your subsequent ability to restore to a point in time prior to the last restore, but after the as yet unspotted data disaster that occurred 3 days ago when a developer missed a where clause that no one noticed is still in doubt...

The housekeeping routines that clean up full backups before the new one has completed - and had the restore tested.  The 'test' backup that destroys the differential chaining and is then removed as it was 'just a test' etc.

April 11, 2014 3:55 AM

Greg Linwood said:

RichB - if you have LogShipping running with a common frequency such as every 5 minutes, at least you'll know that the problem has happened quickly enough to minimise the risk by doing something like (a) finding the offending backup & manually restoring it to your log shipped server (b) take a fresh full or diff backup so that further log backups are at least restorable.

April 11, 2014 4:27 AM

Ian Yates said:

I too have found this with a brand of disk show backup software.

Their problem was slightly different in that they set VSS flags such that SQL treated the disk image as a full backup and there was no way to change it.

The built-in Windows disk image backup utility seems to do the right thing in that you get to choose full vs copy.

April 11, 2014 8:30 AM

Tibor Karaszi said:

A few days ago, we were doing restore practice drills with a client. I had tested the stuff before this,

November 3, 2014 1:11 PM

Victor Brink said:


Seems likely that the NULL was the name of the file, and the true identity of the device is probably a GUID (physical_device_name)... that's because the 3rd party programmers neglected to populate a nullable column in backupset...

Refer the log: exec

sp_readerrorlog 0,1,'virtual_device',null

Problem is the way VSS copy databases (SNAPSHOT) and how the log backup is configured...

SELECT Name, Database_Name, physical_device_name, is_copy_only, device_type, is_snapshot

FROM backupset BS

JOIN backupmediafamily BMF

ON BMF.media_set_ID = BS.media_set_ID

WHERE BS.database_name = DB_NAME()


ORDER BY backup_start_date DESC

The logs you gotta look after separately, and if IS_COPY_ONLY is 1 you are good to go with FULL/DIFF/LOG routine... else not.

December 12, 2014 9:13 AM

TiborKaraszi said:

No Victor, that was *not* the snapshot backup. And it wasn't NULL, it was indeed the filename nul. Right before thesnapshot backup (which also had an entry in backup history) we had this *log* backup.

As a veteran DBA, we recognize the old trick to backup the log to the filename nul to emtpty the log without actually producing a backup file. This was what the backup software was doing.

So, as I mentioned, we *also* had an entry for the snapshot backup. This has it's own issues (not being copy_only, and after we got a new version making it copy_only the fact that SSMS still tried to restore from the GUID as a filename), but that is a different topic. See if you want.


December 12, 2014 2:20 PM

Adam said:

We had a similar issue with virtual SQL servers and VSS snapshots.

I just disabled the SQL VSS writer service so that the 3rd party backup tool cannot interact with SQL.

Chains remain intact now.

December 16, 2015 10:43 AM

TiborKaraszi said:

That is indeed an option. Adam. Disabling the SQL VSS Writer service, that is. For the benefit of other readers, I just want to point out clearly that one shouldd now be aware that snapshts produced now *is not to be trusted* regiarding the SQL Server databases. I.e., restore from such a snapshot, and expect corrupt databases (i.e., you now want to restore each database from other backup means like SQL Server bacup files).

December 16, 2015 4:21 PM

David D said:

"I just want to point out clearly that one shouldd now be aware that snapshts produced now *is not to be trusted* regiarding the SQL Server databases. I.e., restore from such a snapshot, and expect corrupt databases". Sorry for my misunderstand, but are you saying that if the SQL VSS Writer Service is disabled the backup will still run but the backup can't be trusted? Or are you say we can't trust snapshot backups at all?

January 11, 2016 9:06 AM

TiborKaraszi said:

Thanks for asking for elaboration, David. If you have doubts, other readers might as well.

What I'm saying is that snapshots are not to be trusted *when you disable the SQL Server VSS Writer service*.

The backup will run, but there is no communication with SQL Server, and you will likely have databases that after a restore of such snapshot will be corrupt.

You can trust snapshot when properly implemented. This includes

a) That you didn't disable the SQL Server VSS Writer service.

b) That the backuo software bothers to tell SQL Server that it does a snapshot. So that SQL Server can do its stuff. One vendor, for instance, exposes this as an option which they call "Enable application-aware processing". You don't want to "uncheck" this if you want your SQL Server data to be OK when applying the snapshot ("restore from the snapshot").

January 11, 2016 10:22 AM


@GrekL: BACK'll break also Log Shipping. The only exception I see now is when previous log backup and current log backup (TO DISK = 'nul') will have the same Last LSN.

November 8, 2017 10:54 PM

Leave a Comment


This Blog


Privacy Statement