Database Snapshots

Steve Jones explains database snapshots:

I’ve rarely dealt with database snapshots, outside of lab experimentation. They didn’t exist when I did most of my DBA work, and since then we haven’t seen the need for them at SQLServerCentral, though, I may suggest we add them to our deployment process since we can quickly roll back if something breaks.

However, I created one recently for a test and realized that I’d forgotten the syntax. Unlike the quick “create database xx” syntax I often use, with a snapshot I need to be more specific.

Word of warning:  don’t have more than one active snapshot of a single database.  If you do, you’ll likely have major performance problems.  My favorite use case for snapshots was building some semi-automated integration tests a few years back.  I created a tool for devs to create snapshots, and then they could run all the tests they wanted and revert the snapshot afterward.  There are some good uses in production environments as well.

Related Posts

Handling MAXDOP On Azure SQL Database

Arun Sirpal plays with MAXDOP settings on Azure SQL Database: Can we change it? No. EXEC sp_configure 'cost threshold for parallelism', 10; GO RECONFIGURE; GO Msg 2812, Level 16, State 62, Line 9 Could not find stored procedure ‘sp_configure’. Msg 40510, Level 16, State 1, Line 11 Statement ‘CONFIG’ is not supported in this version of SQL Server. […]

Read More

Database Migration With dbatools

Jess Pomfret shows how easy it is to migrate databases from one SQL Server instance to another using dbatools: Now that there are no connections we can move the database.  Depending on the situation it might be worth setting the database to read only or single user mode first. In my case, I had the […]

Read More


January 2016
« Dec Feb »