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?

USE DBA
GO


CREATE PROCEDURE usp_CreateDBSnapshot @DBName sysname
AS 

/******************************************************************
Sample exec:
exec usp_CreateDBSnapshot 'MyDatabaseName'

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

    DECLARE
        @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
-----------------------------
    SELECT
        @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

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

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

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

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

-------------------
-- create snapshot
-------------------
--PRINT @Sql

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

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