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?

This blog has moved! You can find this content at the following new location:

http://sqlblog.karaszi.com/does-your-3rd-party-backup-software-ruin-your-sql-server-backup-plan/

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

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
 

Victor Brink said:

Hi

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()

AND TYPE <> 'L'

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 http://sqlblog.com/blogs/tibor_karaszi/archive/2014/11/03/can-you-restore-from-your-backups-are-you-sure.aspx 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
 

SGBD/DBMS said:

@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

(required) 
(required) 
Submit
Privacy Statement