Database Snapshot

                                                --Database Snapshot
A database snapshot is a read-only, static view of a SQL Server database. A database snapshot always resides on the same server instance as its source database.


USE master
GO
-- Drop database snapshot if it already exists
IF  EXISTS (
         SELECT name
         FROM sys.databases
         WHERE name = 'MYDB_snapshot'
                   )
DROP DATABASE MYDB_snapshot
GO
-- Create the database snapshot
CREATE DATABASE MYDB_snapshot ON
( NAME = MYDB , -- FileName(Data)
FILENAME =
'C:\PYamani\SQL_Archives\MYDB_snapshot.ss' )
AS SNAPSHOT OF NJSBCL;
GO

-- Dropping the SnapShort database

DROP DATABASE NJSBCL_snapshot1

Notes:

• The source database must be in Online.
• To create a database snapshot on a mirror database, the database must be in the SYNCHRONIZED mirroring state.
• All recovery models support database snapshots.
• You cannot Drop a database,Detached or restored which holds the at least one Snapshot.
• Files cannot be dropped from the source database or from any snapshots.
• You cannot take a backup for the snapshot database.
• You can have 'N' number of snapshots on a single database, It is depends on the disc space.

    Each database snapshot persists until it is explicitly dropped by the database owner.

-- Testing snapshot by creating new tables
    Once you create a snapshot, you can create a table on source/base database. You can refresh the snapshot tables now to see the new table.
        Create table TestTable (Sno INT)
        Drop table TestTable