CreateSnapshot.sql 1.36 KB
Newer Older
dwentzel's avatar
dwentzel committed
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50
/*
creates a database snapshot of the current database, placing the NTFS sparse files into the same folder
as the actual datafiles.  PRINTs out the command to restore the database back to the snapshot.

I use this to test quickly some code where transaction control may not be possible and I know I want to quickly
rollback my changes to the database.  

EXEC dbo.CreateSnapshot 'Test_SNAP'

*/
if exists (select * from sys.objects where object_id = object_id('dbo.CreateSnapshot'))
BEGIN
	drop proc dbo.CreateSnapshot
END
GO

CREATE PROC dbo.CreateSnapshot( 
	@SnapshotName varchar(255)
)
AS
BEGIN


DECLARE @exec_sql VARCHAR(MAX) 

-- get logical file names
SELECT name, physical_name
INTO #FileNames
FROM sys.database_files
WHERE type = 0 --rows data, no ldf

SELECT @exec_sql = 'CREATE DATABASE ' + QUOTENAME(@SnapshotName) + ' ON ' 
SELECT @exec_sql = @exec_sql + CHAR(13) + CHAR(10) + 
		'(NAME =' + QUOTENAME(Name) 
		+ ', FILENAME = ''' + physical_name + '.snap''),' 
FROM #FileNames

SELECT @exec_sql = LEFT(@exec_sql,len(@exec_sql)-1) + CHAR(13) + CHAR(10)  + ' AS SNAPSHOT OF ' + DB_NAME() 

PRINT 'This is the command to run'
PRINT @exec_sql 
--EXEC (@exec_sql)

PRINT 'This command can be used to RESTORE your db from the snapshot'
PRINT 'RESTORE DATABASE ' + DB_NAME() + ' FROM DATABASE_SNAPSHOT = ' + @SnapshotName + ';'


END;

GO