Quick one today:

Another one of those tasks that keep re-occurring every few days; It is time to blog about it.

Database snapshots are a great feature in Sql Server. It is easy to create and brings about immense value with it. In our lab environment, we use it a lot — and for the right reasons.

Let’s imagine a database DBADB with 3 data files (MDF’s & NDF’s) and one log file (LDF). Now, before making any DML changes, a snapshot is created to preserve the state of the database before any changes. Snapshot is the easiest and best way to do it — when restoring from FULL backups take a long time.

Snapshot databases capture any changes that happen on the main database and preserve the old version of each data page upon any change. This sounds simple and it is, but they way Microsoft implements it is remarkable. You may want to read all about it here. When you run a DML operation, a set of data pages are change/updated. Those pages, before changes, are copied to snapshot database; Changes are then made to the main database.

So, for every data file (MDF & NDF) on the main database, we need a data file (.SS) for the snapshot database. For example, let’s take a sample database called DBADB with 3 data files. Below is a sample script to create snapshot for DBADB.

There are several points that you want to keep in mind, when you create snapshots.

• One of them is space.
  • Snapshot files (.SS) take up as much space as the original data file (MDF or NDF), so make sure you have enough space. Sql Server is making sure that it has enough space in case all the data pages are changed in a data file — smart and safe.
• Only available in Enterprise edition.
• All recovery models are supported
• Must drop snapshot databases before dropping main database. If not you’ll run into error like the one below:

Msg 3709, Level 16, State 2, Line 1
Cannot drop the database while the database snapshot “DBADB_Snapshot” refers to it. Drop that database first.

Now, when you need to restore to previous state, you just RESTORE from snapshot database as shown below. This is much faster.

_Sqltimes