Category Archives: SQL Server Database Snapshot

Understanding Database Snapshot


Database Snapshots is a new feature introduced in SQL Server 2005.

It provides a mechanism that enables us to save an exact read only copy of a database when the snapshot was taken.

It would be highly beneficial that a database snapshot is taken when certain bulk changes to the table data is made or you need to maintain an exact copy of your database as it was at the end of the day, each day.

Some points to be noted while working with Database Snapshots.

 

Database snapshot can be created using T-SQL script only, it cannot be implemented using the Management Studio.

  • Database snapshot of a database should exist on the same SQL Instance as the Source Database. Why this is so would be beyond the point of discussion for this article, so I would explain in another blog soon.
  • Database snapshots cannot be backed up. (But in the Object Explorer you will see that the menu options are available to backup)
  • Database snapshot cannot be updated or modified (That is why it is referred as read only Copy)
  • It is available only in Enterprise Edition of SQL Server 2005.

 

******* Database Snapshot ***************
Example:

/****** Create CyberArk database Snapshot *******/
USE MASTER
GO
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]
USE master
GO
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.

*******/

USE MASTER
GO
DROP database [QEPVAIM-R]
GO

USE MASTER
GO
Drop database [QEPVBG-R]
GO

— You can even plan for an SQL job, if need this Snapshot to be created for specific time intervals.

Undesrtanding SQL Server Database Snapshot


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.

 
What are the advantages?

 

  • 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.

 
What are the disadvantages?

  • 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.

Dropping:

To get rid of a database snapshot, drop the snapshot just like you would drop any other database.

 DROP DATABASE Northwind_Snapshot1000

Restoring:

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'

 

 

Summary

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.

Example :
******* Database Snapshot ***************
Example:

/****** Create CyberArk database Snapshot *******/
USE MASTER
GO
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]
USE master
GO
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.

*******/

USE MASTER
GO
DROP database [QEPVAIM-R]
GO

USE MASTER
GO
Drop database [QEPVBG-R]
GO

— 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.

 

%d bloggers like this: