Easily Create Database Snapshots

Creating database snapshots can be a bit tedious, especially on databases with multiple files.

A workmate put the following script together to take away the drudgery. Just give your database name and desired snapshot name to the following script and it creates a database snapshot for you using the same data directories as the live database.

If you want the snapshot data files somewhere else, you can comment out the EXECUTE statement then modify the generated script with your preferred file location.

Notice the $CURSOR$ keyword. This makes a handy SQLPrompt snippet!


/*
This script will create a snapshot of the source DB, it will handle multiple
files in the DB.  It also supports multiple snapshots of the same source by
changing @SSName.
  
   Set @SourceDBName to the name of the DB you want a snapshot of.
   Set @SSName to what you want to call your snapshot.
  
You can disable the Execute statement at the end if you just want to get
the TSQL code for later use.
*/
DECLARE  @SourceDBName  AS SYSNAME,
         @SSName        AS SYSNAME,
         @SQL           AS VARCHAR(4000)

SELECT
  @SourceDBName = '$CURSOR$',
  @SSName = 'snapshot_name'

SELECT
  @SQL = 'CREATE DATABASE '
           + @SSName
           + ' ON '
           + LEFT(script,LEN(script)
                           - 1)
           + 'AS SNAPSHOT OF '
           + @SourceDBName
FROM  
  (SELECT
    (SELECT  
      col
        + ', ' AS [text()]
     FROM    
      (SELECT   TOP 100
        CASE
          WHEN RIGHT(mf.physical_name,4) = '.ndf'
          THEN '(NAME = '
                 + mf.name
                 + ' ,FILENAME = '''
                 + REPLACE(mf.physical_name,'.ndf','_'
                                                     + @SSName
                                                     + '.ss')
                 + ''')'
          ELSE '(NAME = '
                 + mf.name
                 + ' ,FILENAME = '''
                 + REPLACE(mf.physical_name,'.mdf','_'
                                                     + @SSName
                                                     + '.ss')
                 + ''')'
        END AS col
       FROM    
        sys.master_files AS mf
        INNER JOIN sys.databases AS db
          ON mf.database_id = db.database_id
       WHERE    db.name = @SourceDBName
                AND mf.type_desc = 'ROWS'
       ORDER BY [file_id]) AS a
     ORDER BY col ASC
     FOR XML PATH('')
    ) AS script) b

EXECUTE( @SQL)

PRINT @SQL