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

Oddity With User Write Count In dm_db_index_usage_stats

Shaun J. Stuart looks at an oddity with the user_updates column on sys.dm_db_index_usage_stats: This pulls both reads and writes from the sys.dm_db_index_usage_stats dynamic management view. A read is defined as either a seek, scan, or lookup and a write is defined as an update. All seemed good until I noticed something strange. One of the top written to tables was, based on our naming convention, a […]

Read More

Safely Dropping Databases

Bob Pusateri notes a little issue when it comes to dropping databases: At a previous employer, we had a well-defined process when dropping databases for a client. It went something like this: Confirm in writing the databases on which servers/instances to be dropped Take a final full backup of databases Take databases offline Wait at […]

Read More

Categories

January 2016
MTWTFSS
« Dec Feb »
 123
45678910
11121314151617
18192021222324
25262728293031