What is Database Snapshot On SQL Server

Database Snapshot is the creation of an unchangeable image of the database, you can think of it as taking current photo of database.

If I explain it through an example it will be clearer.

For example, we take the snapshot of the AdventureWorks2014 database. We are reading through this snapshot. No changes have been made to the AdventureWorks2014 database yet. Our selects go to the original database because there is no change in the original database.

If changes occur in the original database, these changes are written to a dedicated space on the disk for Snapshot (called Sparse Files).

When we want to read the data, if the data has changed in the original database, the unchanged version of the data is read from the sparse file. In this way, the photo becomes intact.

Even though it doesn’t take up disk space as soon as we get Snapshot, the size of the Sparse Files will increase as the changes in the original database increase. If the size of the sparse files grows and there is no space left in the disk, the snapshot becomes suspect and you must delete the snapshot.

It is mostly used with Mirroring technology.

I suggest you read my article “Database Mirroring On SQL Server“. In Mirroring, we cannot read from the secondary database. When we want to read from the secondary database, we can do this by snapshot of the secondary database.

And in this way, we don’t create a burden for the report in the primary database by making our report queries from the secondary database.

Or you can get the snapshot of the database before performing a batch update or delete operation. An incorrect update or delete operation can be reversed using snapshot. It would be a much faster method than returning from Backup.

Snapshot should be on the same instance with the database.

Let’s continue with two examples.

In the first example, we will get a snapshot to be able to read from the secondary database of the mirrored database.

In the second example, we will get snapshot to reverse an incorrect update operation.

Example1:

First, the mirrored database must be synchronous. So the image of your secondary database should be as follows.

We then create the Snapshot with the help of the following script. You must specify all the files in the database.

In our example, there were two file. We created snapshot for these two files. If you are not familiar with FileGroup and File, you can read my article  “How To Create a Database On SQL Server“.

I got the following script from msdn.

With the following script, you can find the size of the snapshot on disk and the maximum size it can grow.

The following result set returned when I ran the script. I set the size of these two data files to 256 mb.

That’s why the snapshot can grow to a maximum of 256 MB.

We created our snapshot.

You can read from your snapshot as if it were a database from the Databases-> Database Snapshots section on SSMS.

Normally, you cant read from secondary database on mirroring. But you can read from the snapshot of the secondary database.

Example2:

Let’s run the script below in the primary database.

After you take Snapshot, delete any records in any table with the help of the following script. I am deleting records from the table named Snapshot_deneme that I created earlier in the AdventureWorks2014 database.

After the deletion process, you can restore your deleted records with the help of the following script.

But if you have done another operation after taking snapshot, you’ll lose those changes.