Snapshot for Database SQL

use demostart
go

exec sp_helpfile
--creating snapshot database

create database demostart_Snapshot
on(name=demostart_Data,filename='C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\demostart_Snapshot.snap')
as snapshot of demostart

-- Reverting the soure database using snapshot database

use master

Go
Restore database demostart
from database_snapshot='demostart_Snapshot'


--to monitor waits caused by snapshot see REPLICA_WRITES in wait_type column

select * from sys.dm_os_wait_stats
where wait_type='REPLICA_WRITES'

--Droping snapshot database

Drop database demostart_Snapshot