We have code deployments every Thursday, some of the changes involve database changes - before the code is depolyed we do a SQL backup in case there are errors, this usually takes 45 minutes and everyone has to wait till it finishes. Could I take advantage of taking a database snapshot, and if the code tests good just delete the snapshot, if there is a problem use the snapshot to roll back any changes made?

  • We do this often, take snapshot before change and delete if no issues ON TOP of regular backup though (plus 1-minute TLOG backup). DB Snapshot is a godsend in those cases. We've had multiple VMware upgrade redo's (upgrade, error, fix error, restore snapshot, try again)
    – Jerry Hung
    Nov 7 '19 at 7:54

Depending on the changes being performed, database snapshots are a viable alternative to backups. You need to ensure you're meeting the prerequisites and that you won't be impacted by the limitations for the source and snapshot (see here).

There are limitations around certain feature usage, such as no MEMORY_OPTIMIZED_DATA filegroups, that prevent snapshots. In addition, some features like FILESTREAM filegroups and Full Text Indexes are unavailable in the snapshot database.

You also need to ensure you have sufficient disk space available when undergoing post-deployment testing because the snapshot will grow as you change data.

You also can't drop database files from the source DB after a snapshot is created. In circumstances where this is happening, backup is the solution.

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.