The advent of SQL 2005 gave us the power of Database Snaphots. What are Snapshots? They’re just great! Someone with a lot more time than me has already written a detailed blog post on the subject: http://www.simple-talk.com/sql/database-administration/sql-server-2005-snapshots/. Unfortunately Management Studio did not come with GUI support for them, which is a real shame considering how often I use them.
However, I have written a script which I keep as a SQL Server Template (easily accessible via Template Explorer). Maybe it’s useful to you also..?
/*
Description: Script to create a Database Snapshot of the current database
Author: Nick Allan
Date: Mar 2008
*/
DECLARE @Reference nvarchar(10)
SET @Reference = ‘FBCXXXX’
DECLARE @SQL nvarchar(max), @SnapshotSuffix nvarchar(100) ,@SnapshotName sysname
SELECT @SnapshotSuffix = ‘_Snapshot_’
+ IsNull(@Reference + ‘_’, ”)
+ Substring(suser_name(), CharIndex(‘\’, suser_name()) + 1, Len(suser_name())),
@SnapshotName = DB_Name() + @SnapshotSuffix
IF DB_ID(@SnapshotName) IS NOT NULL
BEGIN
PRINT ‘–Database already exists… DROP DATABASE ‘ + DB_Name() + @SnapshotSuffix
RETURN
END
SELECT @SQL = ‘
CREATE DATABASE ‘ + @SnapshotName + ‘
ON (
NAME = ‘ + [name] + ‘,
FILENAME = ”’ + LEFT(physical_name, Len(physical_name) - 4) + @SnapshotSuffix + ‘.ss”
)
AS SNAPSHOT OF ‘ + DB_Name()
FROM sys.master_files
WHERE database_id = DB_ID() AND type = 0 –Data
BEGIN TRY
EXEC (@SQL)
PRINT ‘Created DB snapshot: ‘ + @SnapshotName
END TRY
BEGIN CATCH
DECLARE @ErrorMessage nvarchar(max), @ErrorSeverity int
SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY()
RAISERROR ( @ErrorMessage,@ErrorSeverity,1 –Error State
)
PRINT @SQL
END CATCH
