2

We are researching the direction of using Snapshot backups for SQL Server, rather than more traditional backup methods. We are using CommVault (and would be for snapshot too..)

I am trying to understand, better, how they work compared to traditional backups, or agent backups using CommVault. As I am not really a storage guy, I don't quite understand how the restore processes work from a snapshot backup for a SQL Database.

I do understand the absolute speed benefit, but would like to hear more about the pro's and con's of utilizing snapshot backups for SQL Server.

3

Cons:

  • necessitates installing the add-on for Commvault prior to running a restore. In an emergency, this can add a brutal layer of indirection.

  • prevents piece-meal-restores of individual file groups, since the entire database must be restored to be consistent.

  • You need to know how to use Commvault, instead of just the built-in tools.

  • backup history is obfuscated inside SQL Server, such that you only see a guid as the target of a backup.

Pros:

  • you may get better compression of the backup file

  • storage guys feel like they're managing backups (maybe a con, quite frankly)

  • backup process may be faster.

There are undoubtedly a metric ton of other pros and cons I'm not thinking of.

Personally, as a DBA, I'd way rather use SQL Server native backups.

An exercise that might help you decide on an appropriate methodology might be to get the business folks to thoroughly evaluate their recovery point objective and recovery time objective.

If they can afford to lose 24 hours of data in those databases, probably nightly backups are ok. If you can't afford to lose more than, say, 15 minutes of data, you'll need 15 minute log backups whenever data is being written to the database as well as full backups. If the VDI/VSS backup solution doesn't offer log backups, or they can't afford the space for frequent full backups, then you know you cannot use that kind of system. As for recovery time objective, if they can put up with a week of downtime after a disaster occurs, then you're probably ok to go with a VDI/VSS-based backup system.

  • 1
    +1 I always felt the snapshots tended to complicate the whole process in most cases. Unfortunately most orgs don't tend to use it as a one-off for systems with unique needs, but rather as a one-size-fits all solution. – LowlyDBA May 2 '18 at 15:39
  • 1
    yah, they love to do a network-wide one-size-fits-all solution. I hate them for that. – Max Vernon May 2 '18 at 15:42
  • 1
    Used commVault before (in 2014/15), the restore process is a pain esp. when you need to restore multiple dbs to different server because people have to use GUI (the command line interface is way complex if you do not spend enough time to automate it). Lots of time, db backup tool is not chosen by DBA team but by some other team (or person). The chosen tool is one-size-fits-all tool, like backing up files, mailboxes, etc, etc. – jyao May 2 '18 at 15:51
  • 1
    Thanks for all the input Max et al. Now that I am sufficiently terrified, I will push for traditional backup methods rather than this fancy, new age, wizardry! :D Nightly Full's, here I come! (data warehouses...) – Wes May 2 '18 at 18:33
  • 1
    Does the ETL process completely re-load the data, or is it just an incremental load? If it's incremental, you could consider a once-per-week full backup, with nightly incremental backups as a way of reducing the backup time during the week. You might ask another question about backup strategies for your data warehouse databases. – Max Vernon May 2 '18 at 18:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.