Home Dashboard Directory Help

Option to force VSS backup to copy_only by Stan Segers



Sign in
to vote
Type: Suggestion
ID: 790560
Opened: 6/20/2013 6:46:51 AM
Access Restriction: Public


With many of today’s systems being virtual these days and a plethora of backup tools that do VSS based backups of whole virtual machines, including SQL Databases, I regularly get confronted with damaged backup strategies. The VSS backups of VMs often hurt in one of two ways (or even both). Differential backups being based on a snapshot instead of the intended backup and transaction logs being truncated by the VSS snapshot (thus breaking the log chain).

Unfortunately virtualization admins implementing these snapshot technologies often have no clue of the impact on SQL Server (if they know about the fact they are touching a SQL Server at all). So in order to protect the backup strategy, I would like configuration option in SQL Server to force VSS backup commands to copy only and ignore log truncations through VSS. IMO it could be an advanced configuration option through sp_configure.

EXEC sp_configure 'force copy_only backup for VSS', 1
Sign in to post a comment.
Posted by Adam Machanic on 1/24/2014 at 8:51 AM
This is about more than just protecting backup plans. It would also allow customers to fully leverage existing SAN technology (which may not support COPY_ONLY) on Availability Group secondary replicas. Today snapshots must be taken on the primary, which is highly limiting.
Posted by Stan Segers on 7/26/2013 at 1:58 AM
I found the culprit on the TLog part and created a seperate connect-item(https://connect.microsoft.com/SQLServer/feedback/details/795081).
Posted by Stan Segers on 6/25/2013 at 2:34 PM
My bad on the T-Log part... I had a problem with Mirrored and Logshipped SQL 2005 database a couple of years ago; physical + virtual partners and logshipping was backing up either (sync with witness) to get a copy offsite every 5 minutes. This worked well if the database was principal on the physical box, but log shipping restores would fail if the virtual box was principal around Sunday 16:30 (most of the time the physical server was principal, so this was rare to begin with). Anyway... picture different contractors managing different parts of infrastructure, problem occurs during the weekend and one hand does not know what the other does. I did track it down to snapshots being taken and that policy was confirmed by my peers. In the end I found myself scheduling an extra diff backup and restore that if a failure of restores occured.

For the speculation part, I assume the snapshot software did a truncate log before/after pulling the snapshot, maybe switched recovery models back and forth between FULL and SIMPLE or something else that invalidated the logchain. Bottom line, I may have been blaming too much on VSS, but any additional protection I can get for the T-Logs would be very nice.
Posted by Microsoft on 6/25/2013 at 11:32 AM
Thanks for your suggestion.
Currently, VSS backups are only database-level (full or differential) backups, and as such do not have any impact on log truncation. Only transaction log backups will cause log truncation, so you should be safe on that point.
I do see the concern on differential backups, and we will consider it, but it is a bit late for the coming release, so it will need to be considered for a future release.
Sign in to post a workaround.