Database Snapshot

Database Snapshot

Overview

• Introduced in 2005
• Database snapshot is static, read-only view of source database at the time when snapshot was created minus uncommitted transaction
• Dependent on source database
• Physically available on server where source db is there
• Will be accessible as long as source db is there
• Not same as backup
• Multiple snapshots can be created

Behind the Scene

• Operates at data page level
• Before the page of source db is modified for the first time, original page is copied to snapshot
• Creation of snapshot does not take time as well as space as it will be empty
• Gradually it will keep storing all original data pages which have been modified after taking the snapshot

Creating Database Snapshot

• *.mdf and all *.ndf files would be taken into consideration while creating database snapshot
• Extension is .ss
• Syntax

CREATE DATABASE SourceDatabase_Snapshot ON -- Snapshot Name

(

      NAME = SourceDatabase, -- Logical FileName of Original Database

      FILENAME = 'C:\SourceDatabase.ss' -- Location of File Name with .ss ext

)

AS SNAPSHOT OF SourceDatabase -- Database Name

• Now when you check size of this .ss file, it will be same as .mdf file of Original Database, but actually it will empty. When we check the free disk space in My computer of that particular drive, it won’t change after creating snapshot.

Using Database Snapshot

• After creating, snapshot will not be available in Object Explorer, however it will be available in Database dropdown in SSMS.
• We can say Use SnapshotName OR select * from SnapshotName.dbo.TableName

 Use of Database Snapshot

• History Maintenance
• Reporting purpose
• Before doing any major update, we can create a snapshot. If operation was not successful, we can revert back to original stage 

Reverting snapshot to Original Stage

• We can go back to original stage from the point of time when we took the backup.
• Limitations

1. Source db should not contain any read-only OR offline files
2. Only one snapshot should be there
3. After reverting, Transaction log backups won’t work, so full backup has to be taken

• Syntax

RESTORE DATABASE <database_name> FROM DATABASE_SNAPSHOT =<database_snapshot_name>

Drop snapshot

DROP DATABASE SalesSnapshot0600

Know your Snapshot database in SQL Server

A snapshot database is a read-only purpose and  static view of source database. The database snapshot is transactionally consistent with the source database as of the moment of the snapshot's creation.

1.       How do you create snapshot Database? Using

      T-SQL on Query Analyser

CREATE DATABASE [AdventureWorks_dbss1843] ON

( NAME = N'AdventureWorks_Data', FILENAME =N'E:\MSSQL\Data\AdventureWorks_data_1800.ss' )

AS SNAPSHOT OF [AdventureWorks]

GO

2.       Does snapshot database contain log file?

No

3.       If my snapshot database become suspect can you fix it ?

No

4.       Can you backup/restore snapshot database?

No

5.       Can you change recovery model for snapshot database?

No

6.       Can i create table/view in the snapshot database?

No

7.       Can i  use SSMS to create a snapshot database?

No

Source: Microsoft & Paul S Randal(SQLSkills)

How to create a Snapshot database

sp_helpdb MyTest
go

-- The following script is easiest way to create a Snapshot database.

CREATE DATABASE MyTest_snapshot_db ON
( NAME = MyTest,
FILENAME = 'D:\MSSQL\DATA\MyTest_snapshot_db.ss' )
AS SNAPSHOT OF MyTest;
go

Note: Remember you can't create a snapshot database using SSMS but you can delete snapshot database using ssms.