Dynamically Generate Database Snapshot Creation Script

Written by belle
Posted May 3, 2012 at 10:26 pm

Just a short snippet to dynamically generate a database snapshot script. Could be useful especially if your database has multiple files and/or filegroups:

-- run with "Results in Text" mode
DECLARE @dbname VARCHAR(100) = 'SampleDB'
 
-- we will timestamp both the snapshot name and the files
DECLARE @currdate VARCHAR(20)
SET @currdate = '_' + CONVERT(VARCHAR(20), GETDATE(), 120)
SET @currdate = REPLACE(@currdate, ':', '')
SET @currdate = REPLACE(@currdate, '-', '_')
SET @currdate = REPLACE(@currdate, ' ', '_')
 
DECLARE @SQL VARCHAR(MAX) 
SET @SQL = ''
SELECT
   @SQL = COALESCE(@SQL + ('(NAME=' + [name]
                           + ',FILENAME=''C:TempSnapshots' + [name]
                           + @currdate + '.ss''),'), '')
FROM
   sys.database_files
WHERE
   type_desc = 'ROWS'
ORDER BY
   [file_id] 
 
    -- need to remove last comma
    SET @sql = LEFT(@sql, LEN(@sql) - 1)
SELECT
   'CREATE DATABASE '+ @dbname+ @currdate+' ON ' + @sql + 
   ' AS SNAPSHOT OF ' + @dbname