Database Snapshot Part III

Database Snapshot Part III

How to create database snapshot

Create database snapshot_name on (Name=logicalfilename, filename=’path of snapshot file’) as snapshot of databasename

Create Database KalyanDB_Snap ON

 (NAME = 'KALYANDB', FILENAME ='C:\TEMP\KALYANDB_SNAP.SP') AS SNAPSHOT OF KALYANDB

If you observe the file which is created on c:\temp folder the size of file shows similar to size of database, but if you observer size on disk, it shows the exact value

To view the snapshots

SSMS – Databases – Database Snapshots

 

To retrieve information using snapshot

use KalyanDB_Snap

select * from dbo.OrderDetails

To drop snapshot

DROP database KALYANDB_SNAP

If database has multiple ndfs then we need to create snapshot files for each data file otherwise it will throw the below error

Msg 5127, Level 16, State 1, Line 2
All files must be specified for database snapshot creation. Missing the file

Syntax to create database snapshot for database which has multiple ndf files

create database salesdb_snap1 on

(name = 'primary_data', filename='c:\temp\sales_db_snap.sp'),

 (name = 'Sales2010', filename = 'c:\temp\sales2010.sp'),

 (name = 'Sales2011', filename = 'c:\temp\sales2011.sp'),

 --(name = 'Sales2012', filename = 'c:\temp\sales2012.sp'),

 (name = 'Sales2013', filename = 'c:\temp\sales2013.sp') as snapshot of salesdb

For which databases snapshot are created

select name, database_id, source_database_id from sys.databases where source_database_id is not null

To restore database from database snapshot

restore database kalyandb from database_snapshot='KALYANDB_SNAP'