Create SNAPSHOT In SQL Server

SQL SERVER 2005 + (Only Enterprise Edition) 

SNAPSHOTS are read-only views of actual database of a  particular time and are really useful for sharing load for reporting purpose. You can have multiple snapshot against a single database.

Working of SNAPHOT ?

Suppose you have created SNAPHOT of database at 9:00 AM. You will see that (.ss) snapshot file size is very small initially and as you modify actual database data, the size of this (.ss)  file increases. This is because whenever user modifies actual database record the older records (only First time since snapshot was created) are moved to snapshot database file (.ss). This is done to maintain SNAPSHOT State. In our case it is 9:00 AM

So one must keep this in mind that if you are planning to have multiple snapshot then it may be an overhead and come up as performance bottleneck.

How To Create SNAPHOT ?

Syntax :

Create database <SnapShotName> On
(Name =<ActualDatabase Data FileName >, FILENAME='.ss File Path')
As SNAPSHOT OF <ACtual Data Name>

How To Check Actual Database Data File Name ?

Example : To Create SNAPSHOT

Create database CDB_SNP On
(Name =CDB, FILENAME='D:\CDB_NSP_Data.ss')
As SNAPSHOT OF CDB
Go

As You Can Create Multiple SNAPSHOT

Create database CDB_SNP2 On
(Name =CDB, FILENAME='D:\CDB_NSP_Data2.ss')
As SNAPSHOT OF CDB
Go

 

See Your SNAPSHOT In SSMS Object Explorer