Easy SQL Server Snapshot Script / Proc

Free copy of SQL Best Practices?

Get your copy of SQL Best Practices and be the database developer that has rocket fuel in his veins.
Even better? These Best Practices are written in an easy to understand format so your whole team will quickly want to adopt them.
Get SQL Best Practices

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.

        @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!


Tags: , , , , , , ,

No comments yet.

Leave a Reply