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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: