Create a Database Snapshot

Sometimes it’s handy to be able to quickly revert to a previous version of a database. During testing, it’s handy to set up your test data, snapshot the database, and test over and over again without having to restage the data each time. Backups can be time-consuming in a large database. Snapshots to the rescue!

Prerequisites:

1. Obviously, you’ll need to be using a version of SQL Server that supports snapshots.
2. You’ll need the appropriate permissions on the database server. The fixed server “sysadmin” role will suffice, naturally.
3. You’ll need the appropriate permissions on the directory(ies) where your database files are located. If not, you can always change the script to use a different directory.

Set your SSMS to output results to text (Ctrl + T) and execute the following:

SET NOCOUNT ON

DECLARE @currdate VARCHAR(20)
SET @currdate = CONVERT(VARCHAR(20), GETDATE(), 120)
SET @currdate = REPLACE(@currdate, ':', '')
SET @currdate = REPLACE(@currdate, '-', '')
SET @currdate = REPLACE(@currdate, ' ', '_')
SET @currdate = SUBSTRING(@currdate, 0, 14)

DECLARE @SnapshotName varchar(100)
DECLARE @CreateSQL VARCHAR(MAX) = ''
DECLARE @RestoreSQL VARCHAR(MAX) = ''
DECLARE @names table ([Name] varchar(100), [Physical_Name] varchar(200))
DECLARE @DatabaseName varchar(100) = 'DATABASE NAME GOES HERE'

------------------------

SET @SnapshotName = @DatabaseName + '-' + @currdate + '-1.ss'
DELETE @names

INSERT @names ([Name], [Physical_Name])
SELECT name, physical_name
FROM sys.master_files
WHERE type = 0
AND (database_id = DB_ID(@DatabaseName))

IF (SELECT COUNT(*) FROM @names) > 0
BEGIN
    SELECT @CreateSQL = @CreateSQL + 'CREATE DATABASE ' + QUOTENAME(@SnapshotName) + ' ON ' 

    SELECT @CreateSQL = @CreateSQL + CHAR(13) + CHAR(10) + 
            '(NAME = ' + QUOTENAME([Name]) 
            + ', FILENAME = ''' + [Physical_Name] + '-' + @currdate + '.snap''),' 
    FROM @names

    SELECT @CreateSQL = LEFT(@CreateSQL,len(@CreateSQL)-1) + CHAR(13) + CHAR(10)  + 'AS SNAPSHOT OF [' + @DatabaseName + ']
'
    SELECT @RestoreSQL = @RestoreSQL + 'RESTORE DATABASE [' + @DatabaseName + '] FROM DATABASE_SNAPSHOT = ''' + @SnapshotName + ''';
'
END  

PRINT '/*'

PRINT @CreateSQL 
PRINT '*/

/*'
PRINT @RestoreSQL 

PRINT '*/'

You’ll get an output that looks like the following (with names and locations specific to your server):

/*
CREATE DATABASE [{databasename}-1.ss] ON 
(NAME = [{filename01}], FILENAME = '{filepath01}-20170419_0802.snap'),
(NAME = [{filename02}], FILENAME = '{filepath02}-20170419_0802.snap'),
(NAME = [{filename03}], FILENAME = '{filepath03}-20170419_0802.snap')
AS SNAPSHOT OF [{databasename}]
*/

/*
RESTORE DATABASE [{databasename}] FROM DATABASE_SNAPSHOT = '{databasename}-20170419_0802-1.ss';
*/

Nothing will actually execute yet. I leave that up to you.

Be careful. Don’t play with snapshots unless you understand the possible ramifications, namely, you could inadvertently trash your databases. There’s a good article with lots more details on MSDN here: Create a Database Snapshot. Enjoy!