SQL Database Snapshot

Create Database Snapshot on Current database:

USE TEST

GO

DECLARE @name VARCHAR(1000),
@filename VARCHAR(1000),
@dbname VARCHAR(1000),
@dbssname VARCHAR(1000),
@dbssfilename VARCHAR(1000)

DECLARE @hour VARCHAR(2),
@minute VARCHAR(2)

SET @hour = DATENAME(HOUR,GETDATE())
IF LEN(@hour) = 1 SET @hour = '0'+@hour
SET @minute = DATENAME(MINUTE,GETDATE())
IF LEN(@minute) = 1 SET @minute = '0'+@minute

SET @dbname = Db_Name();
SET @dbssname = @dbname+'_data_'+@hour+@minute
SET @dbssfilename = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\'+@dbssname+'.ss'

SELECT @name = [name], @filename = [filename]
FROM sys.sysfiles
WHERE groupid = 1;


--CREATE SNAPSHOT
EXEC( 'CREATE DATABASE ' + @dbname + '_dbss' +@hour+@minute + ' ON
( NAME =' + @name + ', FILENAME = '''+@dbssfilename +''')
AS SNAPSHOT OF '+@dbname);


Restore Database to a Database Snapshot:
USE master;
GO
-- Reverting TEST to TEST_dbss1717
RESTORE DATABASE TEST from
DATABASE_SNAPSHOT = 'TEST_dbss1717';
GO

Drop Database Snapshot:
USE master;
GO
DROP DATABASE TEST_dbss1717;