Friday, September 27, 2013

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


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

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'

No comments: