12 August 2009

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

No comments: