Solved

SQL VSS Writer, copy-only backups

Posted on 2014-10-15
7
462 Views
Last Modified: 2014-11-12
I am backing up a SQL database, in SQL 2014 (though if you know the answer pertaining to older versions of SQL, I'd be interested in hearing it), using a 3rd party product. This product utilizes the SQL VSS Writer to perform the backup. I have edited the registry, by creating the following key, with a value of 1.

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\SQLWriter\Settings\DisableLogShrink

This keeps the backup from truncating the logs. The question is, is there a setting that I can add that will cause all of the backups using the SQL Writer to do a copy-only backup?

The reason for this, I am doing snapshots of the database in AWS using a product called CPM. It just creates AWS snapshots, and is a management layer on top. I don't want these backups to register within SQL itself, so that I can perform a second backup from within SQL itself, as well as log backups with truncation. By setting the registry value above, my snapshots do not truncate the logs anymore, but each snapshot shows up as a full backup within SQL. If I can set the backups going through the SQL writer to be copy-only, then they will not show up as a full backup in SQL. And at that point, I can do a separate full backup within SQL itself that doesn't user the writer and subsequent log backups that provide more granular point-in-time recoveries that I can't get with the snapshots.
0
Comment
Question by:PMFM
  • 4
  • 2
7 Comments
 
LVL 20

Expert Comment

by:Marten Rune
ID: 40384450
Here is the Microsoft link to the writer. Though I thing the 3'rd party Product needs to have this function implemented. It clearly states that the writer supports copy_only backups. But it needs to be requested from the requestor, i e the 3'rd party backup program.

I did google the CPM, but with no luck. So I don't Think I can help further.

You need to find documentation from the CPM, or ask their support if this function is implemented.

Regards Marten
0
 
LVL 39

Expert Comment

by:lcohan
ID: 40384481
At the link below it is stated that indeed "SQL Writer supports: Copy-only backup"
http://msdn.microsoft.com/en-us/library/ms175536.aspx

"The backup type (full, copy, incremental) can be specified by a VSS-based backup application at the beginning of the backup session, using the IVssBackupComponents::SetBackupState."

http://blogs.technet.com/b/filecab/archive/2008/05/21/what-is-the-difference-between-vss-full-backup-and-vss-copy-backup-in-windows-server-2008.aspx


For older versions the link below explains how to "Copy-Only Backup.  It is sometimes necessary to take a backup that is intended for a special purpose, for example when you need to make a copy of a database for testing purposes.  This backup should not impact the overall backup and restore procedures for the database. Using the COPY_ONLY option specifies that the backup is done “out-of-band” and should not affect the normal sequence of backups. The SQL writer supports the “copy-only” backup type with SQL Server 2005 instances."

Basicaly "SQL Writer Metadata Document"  which is "an XML document created by a writer (the SQL writer in this case) using the IVssCreateWriterMetadata interface, and containing information about the writer's state and components." where "◦VSS_BS_COPY – supports “copy-only” backup option."

http://technet.microsoft.com/en-us/library/cc966520.aspx#_Backup_and_Restore_Options  Support
0
 

Author Comment

by:PMFM
ID: 40384506
Thanks. I had found in the documentation that it supports it. As well as disabling truncation. What I can't find documented is a way to change the settings on the VSS writer itself. The CPM backup software support suggested a registry key to disable truncation for any backups that use the SQL Writer, whether CPM or another product. That worked perfectly, though I can't find it documented any place else. I was hoping to find the same type of solution for copy-only. The interface and options for the use of VSS in CPM is limited. So, my only chance at getting this will be through a configuration file or registry edit.
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 39

Expert Comment

by:lcohan
ID: 40384536
That's exactly what the XML "SQL Writer Metadata Document" will do for you - right?

<<
Basicaly "SQL Writer Metadata Document"  which is "an XML document created by a writer (the SQL writer in this case) using the IVssCreateWriterMetadata interface, and containing information about the writer's state and components." where "◦VSS_BS_COPY – supports “copy-only” backup option."

http://technet.microsoft.com/en-us/library/cc966520.aspx#_Backup_and_Restore_Options  Support
>>
0
 

Author Comment

by:PMFM
ID: 40384665
Right, but I can't find the location of this .xml file, and it sounds like it is possibly generated on the fly by the writer itself and any changes to parameters would have to be done when calling the sql writer. That may be completely incorrect - it's really just a semi-educated guess. The .xml file is not in the same location as the sqlwriter executable or any of the subfolders.
0
 
LVL 39

Expert Comment

by:lcohan
ID: 40384721
You can do it via "IVssCreateWriterMetadata interface" http://msdn.microsoft.com/en-us/library/aa383589(v=vs.85).aspx
0
 
LVL 39

Accepted Solution

by:
lcohan earned 500 total points
ID: 40384749
in 2014 you can do it via the "IVssBackupComponents interface"
"Applications obtain an instance of the IVssBackupComponents interface by calling CreateVssBackupComponents."

http://msdn.microsoft.com/en-us/library/aa382175(v=vs.85).aspx

you need to set
IVssBackupComponents::SetBackupState VSS_BACKUP_TYPE backupType as VSS_BT_COPY   = 5 - see links below for details:

http://msdn.microsoft.com/en-us/library/aa382833(VS.85).aspx
http://msdn.microsoft.com/en-us/library/aa384679(v=vs.85).aspx
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql 2014,  lock limit 5 32
SQL Improvement  ( Speed) 14 27
SQL View nearest date 5 36
Deal with apostrophe in stored procedures 8 42
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
To efficiently enable the rotation of USB drives for backups, storage pools need to be created. This way no matter which USB drive is installed, the backups will successfully write without any administrative intervention. Multiple USB devices need t…
This tutorial will walk an individual through the process of installing of Data Protection Manager on a server running Windows Server 2012 R2, including the prerequisites. Microsoft .Net 3.5 is required. To install this feature, go to Server Manager…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question