Easy SQL Server Snapshot Script / Proc

Man!!!! Why is the code to create a database snapshot so ridiculously complicated? Actually, I guess I understand why but how about allowing it to be done through SSMS and then setting a bunch of the parameters as defaults for us? Come on Microsoft! Help a SQL brother out!

Well luckily, where Microsoft fails, I'm there for you. Here's my brilliant proc which will automatically create a database snapshot for you. It places the snapshot in the same physical location as the physical file in the snapshot. I think this will be fine in most environments but if you're running tight on disk space or expect your snapshots to get pretty big you've been warned!

I create the proc in a database to hold various utility scripts. I creatively called my database: DBA. Catchy right?


CREATE PROCEDURE usp_CreateDBSnapshot @DBName sysname

Sample exec:
exec usp_CreateDBSnapshot 'MyDatabaseName'

2016-07-29    Steffek    Initial Version. sqldbpros.com

        @Sql NVARCHAR(MAX)
       ,                        -- holds dynamic sql
        @Params NVARCHAR(MAX)
       ,                        -- hold dynamic sql parameters
        @SnapshotDate VARCHAR(8)
       , -- current date
        @DBFiles NVARCHAR(MAX)
       ,                    -- holds list of data files and paths to create
        @DBSnapshotName NVARCHAR(MAX);

    SET @SnapshotDate = CONVERT(VARCHAR(8), GETDATE(), 112)
    SET @DBSnapshotName = @DBName + '_Snapshot_' + @SnapshotDate

-- get snapshot files from database_files
        @Sql = N'

    SELECT @DBFiles = 
        (SELECT '','' +  ''(Name = '' + NAME + '', FILENAME = '' + CHAR(39) +
            REVERSE(SUBSTRING(REVERSE(physical_name)--Reverse the string since the data we want to remove is at the end.
    , PATINDEX(''%.%'', REVERSE(physical_name)) + 1--parse off the file extension by looking for the period.
                      len (physical_name)
                      )) +''_'' +  @SnapshotDate + ''.ss'' + CHAR(39) +  '')''
    FROM ' + @DBName + '.sys.database_files
    WHERE TYPE = 0
    FOR XML PATH('''') )  '

--PRINT @sql

        @Params = N'@DBFiles nvarchar(MAX) OUTPUT, @SnapshotDate varchar(8)'

    EXEC sp_executesql
       ,@DBFiles = @DBFiles OUTPUT
       ,@SnapshotDate = @SnapshotDate;

        @Sql = N'
USE ' + @DBName + ';

CREATE DATABASE ' + @DBSnapshotName + '
ON ' + STUFF(@DBFiles, 1, 1, '') + '
AS SNAPSHOT OF ' + @DBName + ';'

-- create snapshot
--PRINT @Sql

    EXEC sp_executesql
    PRINT 'created database snapshot: ' + @DBSnapshotName


What do you think? Handy? Suggested improvements? Bring it on!


By Phil Steffek

Phil Steffek is a professional sumo wrestler who is hoping to represent the U.S. in the next Olympics. During the off season Phil is a Data Architect working on transactional databases, data warehouse implementations, and all aspects of business intelligence. Also, Phil is not really a sumo wrestler. You can get to know a bit more about my non-sumo doings by checking out my LinkedIn Page

Leave a comment

Your email address will not be published. Required fields are marked *