SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Differential backups rendered useless by backup device


Differential backups rendered useless by backup device

Author
Message
sqlskj4000
sqlskj4000
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 69
You could create an after-insert trigger on 'backupset' table in msdb database. Check for the condition is_snapshot=1 and run the full backup job.
This is a good idea. But luckily for me, something changed in the backup plan of the n/w admins and I didn't see those annoying snapshot entries anymore.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86212 Visits: 45229
rferreira.dba (2/20/2013)
Let me put this another way: Is there any valid reason for Microsoft not to fix this?


Because there's nothing broken here?
The kb article you referenced refers to a problem where an incorrect insert is made into the backup history tables reflecting a backup that did not happen, not the case where you really are taking valid backup of the database. The backup history tables do not drive valid restores, they're just a record of what backups happen. The issue you have is not related to that kb article.

In your case, you are taking two types of full backups and the differential that runs is correctly based on the latest full (whatever it may be). This isn't a bug, this is documented, normal behaviour
You can get into exactly the same mess with two scheduled of native backups, where one is taken off site and not available. A differential backup is always based on the latest non-copy only full backup

Is this possible? Is there an after_successful_snapshot trigger somewhere, or something similar that I can use?


As far as SQL is concerned, that's just a database backup run by a 3rd party product. No less valid or correct than a normal database backup. Can you hook into whatever is running the snapshot backups and have that run some SQL afterwards?

Options:
- Set the backup app to snapshot the machine but exclude the databases. Some tools can, some can't. If you have a good SQL backup strategy, as it sounds like you have, then there isn't a need to snapshot the databases as well.
- Set the backup app to do a 'copy only' snapshot. Probably won't be an option, haven't seen that as a setting in many tools.
- Change the backup strategy to be a mix of full and log backups. If you're not doing differentials then the additional full backups won't have any effect on restoring. Not the best option, probably the last option if nothing else is possible

p.s. This is why I always say that all SQL backups should be done the same way and that there should only be one method for the SQL backups. Native or 3rd party, not a mix of both.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


sqlskj4000
sqlskj4000
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 69
I agree it's not exactly a bug. But the fact remains that differentials are running based on a non-existent full backup (a backup that's part of a volume snapshot). It could've been avoided if the VSS writer recorded it as 'copy only'.
And I agree that there should be only one method of backup. But in my case we require both. Volume snapshot is the preferred restoration method, in case of a crash. SQL maintenance plans are our fallback option.
If we don't have access to the backup device to check it's options, it's either your third option or the option proposed by rferreira (full backup immediately after the snapshot).
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86212 Visits: 45229
sqlskj4000 (2/20/2013)
I agree it's not exactly a bug. But the fact remains that differentials are running based on a non-existent full backup (a backup that's part of a volume snapshot). It could've been avoided if the VSS writer recorded it as 'copy only'.


Agreed, the problem there is that many of the tools that use the VSS writer to back up a SQL database don't have an option for the user to set copy only, and it is not (and cannot be) the default.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Nadrek
Nadrek
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1852 Visits: 2726
Gail beat me to the "change to Full recovery model and do Log backups instead" option.

As far as your proposed "full after each snapshot" option, you can try looking at extended events, as well as seeing if Windows Task Scheduler can trigger off of the snapshot (then you use sqlcmd to run msdb.dbo.sp_start_job), but the brute-force way is to set up a job that polls every N minutes in about the right timerange, and compare the msdb list of snapshot backups to a stateful list of prior snapshot backups. When it sees a new one, kick off a full backup and record the new snapshot.

Personally, I'd have to say that Gail's options are the best - your Full backup after the snapshot may fail for a variety of reasons, leaving your every 4 hour Differential backups silently worthless.
ShuNx239
ShuNx239
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 109
You should disable the SQL Server VSS Writer service. See this discussion.

Also for VMware snapshots it is possible to exclude specific writers by editing vmbackup.conf.
sqlskj4000
sqlskj4000
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 69
ShuNx239 (1/3/2014)
You should disable the SQL Server VSS Writer service.


That is what I eventually did.

11:29pm - net stop "SQL Server VSS Writer"
11:31pm - net start "SQL Server VSS Writer"

:-)
deep_kkumar
deep_kkumar
SSC Veteran
SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)

Group: General Forum Members
Points: 263 Visits: 756
sqlskj4000 (1/6/2014)
ShuNx239 (1/3/2014)
You should disable the SQL Server VSS Writer service.


That is what I eventually did.

11:29pm - net stop "SQL Server VSS Writer"
11:31pm - net start "SQL Server VSS Writer"

:-)


SAN replication has VSS support in Windows that is triggering the SQL Server VSS Writer at backup time. The VSS Writer is effectively creating a snapshot on the database and running a full backup which you probably see logged in msdb database. Try to implement full backups daily and take transaction log backups every 15 mins if possible. If you disable VSS writer you might see transactional integrity issues on the SAN copy. Please double check. If you are not worried about the SAN snapshots then it's good to disable the VSS writer.
rferreira.dba
rferreira.dba
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 156
In our case, our final solution was a bit involved, but for us it is robust and keeps everything we need:

1- We mirrored the database(s) into a mirror server.
2- We now take snapshots of the mirror server and leave the principal alone.
3- And of course, we do not do any backups of the mirror server, except the snapshots.

This way the principal is fine and dandy, has a mirror stand-by to fall back to, and we have the snapshots of the mirror to use during Disaster Recovery exercises. Works perfectly in our environment.

Thanks all,

Raphael
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search