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 ***************
/****** 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.