Database Snapshot in SQL Server 2005:
How does this new feature work?
The initial thought is that the snapshot needs to make a complete copy of your database so the data stays static. If this were the case, the snapshot would offer no advantage over a backup and restore.
Here’s what actually happens: When you create the snapshot, a shell of your database file is created. When data is read from the snapshot, it is actually being read from the primary database. As data changes in your primary database, SQL Server then writes out what the data looked like prior to the data change into a sparse file. The snapshot will then read the sparse file for the data that has been changed and continue to read the primary database for data that has not changed.
If you look at the picture directly below, 90% of the data has not been changed in the primary database, so 90% of the data is still retrieved from here when a query is issued. On the reverse, 10% of the data has changed to satisfy the results for a query that uses the data it would read from the sparse file.
Source: SQL Server 2005 Books Online
As with any new tool or feature, there are always advantages and disadvantages. Here are a couple of key points.
- Since SQL Server only needs to deal with the data that has changed for Database Snapshots to work, the initial creation of the snapshot or subsequent snapshots is extremely fast.
- Data is only written to the sparse file as data changes, so your overall disk space needs don’t double each time you create a new snapshot — as they would if you were using backup and restore for the secondary copy.
- You can create several snapshots for the same database, so you could have rolling snapshots occurring every hour for auditing purposes.
- It is considerably easier to work with snapshots and much faster than backup and restore operations.
- You can create a static reporting environment quickly with minimal effort.
- Corrupt or deleted data can be retrieved from the snapshot to repair the primary database.
- Database changes can be audited with the use of snapshots.
- You have the ability to restore your database using a snapshot.
- Your user community is still hitting your primary database. If you already experience performance issues because of reporting requirements, using snapshots for reporting will not resolve that problem.
- The snapshot is tied to your primary database, so you cannot take the primary database offline. If the primary database goes offline, you won’t be able to access any of your snapshots.
- The Database Snapshots feature is only available in the Enterprise Edition.
- If data changes rapidly, this option may not offer benefits for keeping snapshots around for a long period of time.
- Full-text indexing is not supported in the snapshot.
Creating and Restoring Database Snapshot:
The following guidelines show you how simple it is to create and use database snapshots.
Creating a Database Snapshot:
To create a database snapshot you must use a T-SQL command; managing snapshots is not currently supported through Management Studio. The syntax to create the snapshot is as follows:
CREATE DATABASE Northwind_Snapshot1000 ON (NAME = Northwind_Data, FILENAME = 'C:\MSSQL\Snapshot\Northwind_data1000.ss' ) AS SNAPSHOT OF Northwind
If you wanted to create a snapshot every hour, you could issue the above statement again and just change the name of the database and the file. Now you will have two snapshots, but different static data.
CREATE DATABASE Northwind_Snapshot1100 ON (NAME = Northwind_Data, FILENAME = 'C:\MSSQL\Snapshot\Northwind_data1100.ss' ) AS SNAPSHOT OF Northwind
Usage of Snapshot:
Using the snapshot is just like using any other database. You attach to the database and execute your queries. The thing to remember though is that this is a read-only copy, so you can’t issue UPDATE, DELETE or INSERT statements.
To get rid of a database snapshot, drop the snapshot just like you would drop any other database.
DROP DATABASE Northwind_Snapshot1000
One nice thing about the snapshots feature is that you can restore your entire database back to the point in time when the snapshot was taken. If there were issues with your database and you needed to revert back to the exact time the snapshot was taken, you could use a restore command. Another option is to use snapshots to restore a test environment back to its state when the testing began.
So you can take a snapshot, let your users or developers do their testing and then restore the snapshot so the database is back to the point in time prior to when testing began. To restore a database using a snapshot you can use the following command:
RESTORE DATABASE Northwind FROM DATABASE_SNAPSHOT = 'Northwind_Snapshot1100'
As you can see, Database Snapshots is simple to implement and use. The first time I did I was able to create a snapshot, run some queries and drop the snapshot in a couple of minutes. This is not one of those high-tech features that will take you weeks to figure out or even determine if it is something you should be using. If you haven’t already tried creating and using Database Snapshots, I think you will be surprised by how easy it is.
******* Database Snapshot ***************
/****** Create CyberArk database Snapshot *******/
Create database [QEPVBG-R] on
( NAME = ‘QEPVBG’, — this one is source name
FILENAME= ‘D:\MSSQL2K8R2\QEPVAIM-R_Snapshot\QEPVBG-R.ss’ — giving new name here
AS SNAPSHOT OF [QEPVBG]
Create database [QEPVAIM-R] on
( NAME = ‘QEPVAIM’, — this one is source name
FILENAME= ‘D:\MSSQL2K8R2\QEPVBG-R_Snapshot\QEPVAIM-R.ss’ — giving new name here
AS SNAPSHOT OF QEPVAIM
/****** Drop Database QEPVBG-R – Snapshot DB *** Note that if database snapshot creation fails,
the snapshot is in suspect status and it should be deleted or dropped.
DROP database [QEPVAIM-R]
Drop database [QEPVBG-R]
— You can even plan for an SQL job, if need this Snapshot to be created for specific time intervals.
— Since I planned for every hour on Mirrored database.