Dynamically Generate Database Snapshot Creation Script

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
Advertisements

2 comments

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s