THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - 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 09, 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

Comments

 

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

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement