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
VN:F [1.9.22_1171]
Rating: 9.0/10 (14 votes cast)
VN:F [1.9.22_1171]
Rating: +3 (from 3 votes)