SMSQL - Copy-Only Backup

[ogdenclinic]

We currently use CommVault's Simpana system for our backup system.  We utilize FlexClones to "refresh" our testing, development, and staging environments with copies of production databases.  I have some PS scripts I've written that accomplishes all tasks involved with refreshing these environments--except for taking a consistent snapshot of my SQL LUNs.  Because I use Simpana for our SQL backups (including transaction log backups), I need to be able to take a disk snapshot of the SQL database using the copy_only flag so that I don't screw up my backup chain.  I won't go into the reasons why, but I can't use Simpana for this snapshot creation.

Can I use SMSQL to simply and only create a copy_only snapshot of my SQL LUNs?  And if so, can I do this via the SnapManager PowerShell?  I know I can use SM PS to create a clone and mount it on a separate system, but it's not clear whether I can create the initial snapshot with the copy_only option.  I'd prefer to just create the snapshot and handle the cloning with my script so I don't have to re-engineer the script.

Any help is appreciated.

[jhamoline]

I'd like to use COPY_ONLY in my environment too; so that snapshots do no impede my ability to backup & restore differential backups. Did you ever get a response from Netapp about how to take a COPY_ONLY backup with SMSQL?

[ogdenclinic]

I never officially engaged NetApp on this topic and found a workaround for my scenario.  I no longer have a need for this functionality (for now) but am still interested in any progress made for future reference.

[jhamoline]

Just recently I found myself wanting to use this functionality and found this post in a search but it seemed familiar and it turns out that I opened a support case with Netapp in 2009 about this issue and they responded that it was not possible at the time, and it doesn't appear to have changed since that time. The support engineer told me, in 2009, that he would file a PVR (Product Variance Request) and if there was enough interest they would implement the change. So far, it appears that COPY_ONLY is not high on the list.

One of the major selling points for SMSQL is the ability to create copies of your DB for testing; it seems like this would be a great reason to implement the COPY_ONLY functionality into the product because it would improve the ability to use SMSQL for creating test & staging environments without affecting backup & restore schemes. Without the ability to use COPY_ONLY for testing & staging I will not be able to use differential backups for my VLDB databases which severely limits SMSQL's usefulness to me.

If there are other customers reading this that would like to see the COPY_ONLY functionality integrated into SMSQL please 'like' this post or leave a comment.

Heres a link to COPY_ONLY backups for those DBAs that haven't run across it yet: http://msdn.microsoft.com/en-us/library/ms191495.aspx

[qzhang]

It is highly recommended you consider using SMSQL as your main SQL Server backup tool, instead of a supplementary tool for other backup tools . In such case, you would not need to have COPY_ONLY backup. Since SMSQL full backup is very fast, you can create more frequent full backup without any overheads. That basically eliminate the needs for differential backups. Also SMSQL should be comprehensive enough for all your backup recovery needs.

In any case, I believe COPY_ONLY backup is considered in the next SMSQL release. It is just not that critical to SMSQL from my understanding.

Thanks,

-Qing

[abhisek]

Hi ,

We do not have COPY_ONLY backup option now with SMSQL.However our full backup based on Snapshot technology is pretty fast and reliable.We would recommend you to use SMSQL for SQL Server on NetApp storage compared to other 3rd party applications.

Regards,

Abhishek

[jhamoline]

Thanks for the responses Qing & Abhishek. I am currently using SMSQL as my primary backup & restore tool. Correct me if I am wrong, but a SMSQL snapshot will not protect against a filer bug or hardware failure that causes corruption at the storage layer. I need to an offline backup (on tape, separate filer or separate aggr) to protect against that kind of corruption. Currently I am using a weekly full SQL server backup and tlogs to protect against this kind of failure. The ability to take traditional differential backups without my test & staging backups interfering with the sequence would be a major coup for me. Adding the COPY_ONLY functionality to SMSQL would allow me to have offline backups using differentials (for VLDBs that would be incredibly useful) and still use SMSQL for the test and staging environments.

The addition of the COPY_ONLY function to SMSQL would make my life a lot better. Please consider adding it to the next release of SMSQL.

Thanks,

James

[abhisek]

Hi James ,

SMSQL does  provides you with point in time restore capability.There are DR strategies to combat storage layer failure ,which is very rare.We provide SMSQL integration with SnapMirror and SnapVault to help you recover from storage disaster.

We will evaluate your request for COPY_ONLY function for future releases.

Regards,

Abhishek

[welch]

For those searching on this subject, SMSQL 6.0 now supports Copy Only backups for SQL Server.