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.
Best Answer
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.