Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

A Script A Day - Day 6 - Drop and Create Database Snapshots

Today's Script will drop all database snapshots and create a database snapshot for all online read writeable user databases. I create this script for use in a database mirroring partnership so that the snapshots could be used on the mirroring partner so to impact the mirroring principal less. As such I set the mirroring partnership servers at the beginning of the script because the mirroring partners databases are inaccessible so I have to retrieve the file information from the mirroring principal. This can be changed to run on servers not in a mirroring partnership.

Other than that the only other thing to update is the @SnapshotDirectory variable to the path where you want the snapshots to exist. Each snapshot has a prefix of 'snap_' and a suffix of the time in the format of '_hh00' this was because the snapshots where created on an hourly basis.

/*
      -----------------------------------------------------------------
      Drop And Create Database Snapshots
      -----------------------------------------------------------------
     
      For more SQL resources, check out SQLServer365.blogspot.com

      -----------------------------------------------------------------

      You may alter this code for your own purposes.
      You may republish altered code as long as you give due credit.
      You must obtain prior permission before blogging this code.
 
      THIS CODE AND INFORMATION ARE PROVIDED "AS IS"
     
      -----------------------------------------------------------------
*/

 
/*  
* Name:   spDropAndCreateDatabaseSnapshots 
* Description: This procedure drops all database snapshots and creates a databsae snapshot of 
*     all online read / writable user databases 
* Returns:   
* Source Control:  
* Execution:  EXEC dbo.spDropAndCreateDatabaseSnapshots     
* VERSION CHANGES 
* Release Initials CostBefore CostAfter 
* v1.0   CMc   N/A   0.0033275 
*/ 
DROP PROCEDURE [dbo].[spDropAndCreateDatabaseSnapshots]  ;
GO
CREATE PROCEDURE [dbo].[spDropAndCreateDatabaseSnapshots] 
AS  
BEGIN 
 SET NOCOUNT ON 
    -- Declare Variables 
    DECLARE @MinDBID INT 
    DECLARE @MaxDBID INT 
    DECLARE @DatabaseName VARCHAR(100) 
    DECLARE @SQL VARCHAR(6000) 
    DECLARE @SQL1 VARCHAR(2000) 
    DECLARE @SQL2 VARCHAR(2000) 
    DECLARE @SQL3 VARCHAR(2000) 
    DECLARE @SQL4 VARCHAR(2000) 
    DECLARE @SnapshotPrefix VARCHAR(5) 
    DECLARE @SnapshotName VARCHAR(200) 
    DECLARE @SnapshotSeperator VARCHAR(1) 
    DECLARE @SnapshotHour VARCHAR(3) 
    DECLARE @SnapshotMin VARCHAR(2) 
    DECLARE @SnapshotExtension VARCHAR(5) 
    DECLARE @SnapshotDirectory VARCHAR(100) 
    DECLARE @MinFileID INT 
    DECLARE @MaxFileID INT 
    DECLARE @DBSnapshotName VARCHAR(100) 
    DECLARE @MinSnapshotID INT 
    DECLARE @MaxSnapshotID INT 
    DECLARE @ServerName VARCHAR(15) 
     
    -- Set Variables 
    SET @SnapshotDirectory = 'D:\Snapshot\' 
    SET @SnapshotPrefix = 'Snap_' 
    SET @SnapshotSeperator = '_' 
    SELECT  @SnapshotHour = DATEPART(hh, GETDATE()) 
     
    -- Set the servername for the mirroring partner to pick up file names for each database 
    IF @@SERVERNAME = 'PARTNERSERVER' 
  SET @ServerName = 'PRINCIPLESERVER' 
    IF @@SERVERNAME = 'PRINCIPLESERVER' 
  SET @ServerName = 'PARTNERSERVER' 
 
    -- If time is before 10am then add a leading 0 for consistancy 
    IF LEN(@SnapshotHour) < 2  
        SET @SnapshotHour = '0' + @SnapshotHour 
 
    SET @SnapshotHour = @SnapshotSeperator + @SnapshotHour  
    SET @SnapshotMin = '00' 
    SET @SnapshotExtension = '.snap' 
 
      -- Check for temporary tableS and drop it if it exists 
    IF OBJECT_ID('tempDB.dbo.#Database') IS NOT NULL  
        DROP TABLE [#Database] ; 
    IF OBJECT_ID('tempDB.dbo.#SQL2') IS NOT NULL  
        DROP TABLE #SQL2 ; 
    IF OBJECT_ID('tempDB.dbo.#Snapshot') IS NOT NULL  
        DROP TABLE #Snapshot ; 
         
      -- Create temporary tables 
    CREATE TABLE #Database 
        ( 
          ID INT IDENTITY(1, 1), 
          DatabaseName VARCHAR(100) 
        ) 
    CREATE TABLE #SQL2 
        ( 
          ID INT IDENTITY(1, 1), 
          SQL2 VARCHAR(2000) 
        ) 
    CREATE TABLE #Snapshot 
        ( 
          ID INT IDENTITY(1, 1), 
          SnapshotName VARCHAR(2000) 
        ) 
 
      -- Check for existing database snapshots and delete them 
    IF EXISTS ( SELECT  name 
                FROM    sys.databases 
                WHERE   --snapshot_isolation_state = 1 
                        --AND  
                        name NOT IN ( 'master', 'model', 'msdb', 'tempdb', 
                                          'distribution' ) 
                        AND LEFT(name, 5) = 'Snap_' )  
        BEGIN 
                  -- Insert all database snapshot names into a temporary table 
            INSERT  INTO #Snapshot ( SnapshotName ) 
                    SELECT  name 
                    FROM    sys.databases 
                    WHERE  --snapshot_isolation_state = 1 
                           --AND  
                            name NOT IN ( 'master', 'model', 'msdb', 
                                              'tempdb', 'distribution' ) 
                            AND LEFT(name, 5) = 'Snap_' 
                 
                  -- Set Variables for the drop snapshot loop              
            SELECT  @MinSnapshotID = MIN(ID), 
                    @MaxSnapshotID = MAX(ID) 
            FROM    #Snapshot 
 
                  -- Begin loop to drop snapshots 
            WHILE @MinSnapshotID <= @MaxSnapshotID 
                BEGIN 
                              -- Get SnapshotName 
                    SELECT  @DBSnapshotName = SnapshotName 
                              FROM    #Snapshot 
                    WHERE   ID = @MinSnapshotID 
             
                              -- Build DROP DATABASE COMMAND 
                    SET @SQL = 'DROP DATABASE ' + @DBSnapshotName + ';' 
   
                              -- Try Catch block to execute SQL and handle errors    
                    BEGIN TRY 
                                    -- Drop Database Snapshots  
                        EXEC ( @SQL 
                            ) 
                    END TRY 
                    BEGIN CATCH 
                        SELECT  @DatabaseName,  
                                                message_id, 
                                severity, 
                                [text], 
                                @SQL 
                        FROM    sys.messages 
                        WHERE   message_id = @@ERROR 
                                AND language_id = 1033 -- British English 
                    END CATCH 
   
                              -- Get the next SnapshotName ID 
                    SET @MinSnapshotID = @MinSnapshotID + 1   
                        -- End Loop 
                END 
        END
        
      -- Create Database Snapshots for all Online Read/Writable databases 
    IF EXISTS ( SELECT  name 
                FROM    sys.databases 
                WHERE   name NOT IN ( 'master', 'model', 'msdb', 'tempdb', 
                                      'distribution', 'reports', 
                                      'reportserver', 'reportservertempdb' ) 
                        AND DATABASEPROPERTYEX(name, 'Updateability') = 'READ_WRITE' 
                        AND DATABASEPROPERTYEX(name, 'Status') = 'ONLINE'  
                        )  
        BEGIN 
                  -- Insert Online, Read/Writable database names into temporary table 
            INSERT  INTO #Database ( DatabaseName ) 
                    SELECT  name 
                    FROM    sys.databases 
                    WHERE   name NOT IN ( 'master', 'model', 'msdb', 'tempdb', 
                                          'distribution', 'reports', 
                                          'reportserver', 'reportservertempdb' ) 
                            AND DATABASEPROPERTYEX(name, 'Updateability') = 'READ_WRITE' 
                            AND DATABASEPROPERTYEX(name, 'Status') = 'ONLINE' 
 
            SELECT  @MinDBID = MIN(ID), 
                    @MaxDBID = MAX(ID) 
            FROM    #Database 
 
                  -- Begin Loop 
            WHILE @MinDBID <= @MaxDBID 
                BEGIN 
                              -- Get DatabaseName 
                    SELECT  @DatabaseName = DatabaseName 
                    FROM    #Database 
                    WHERE   ID = @MinDBID 
   
                              -- Build up snapshot string 
                    SET @SnapshotName = @SnapshotPrefix + @DatabaseName 
                        + @SnapshotHour + @SnapshotMin 
         
                              -- Create Start of SQL command to be run 
                    SET @SQL1 = 'USE master; 
                              CREATE DATABASE ' + @SnapshotName +
                              ON ' 
      
                              -- Remove records from table ready for next database 
                    TRUNCATE TABLE #SQL2 
 
                              -- Build command to Insert files into temp table 
                    SET @SQL2 = 'INSERT  #SQL2 
                              SELECT  ''( NAME = '' ' 
                        + '+ '''' + name + '', FILENAME =  ''''' + @SnapshotDirectory + ''' + name + ''' 
                        + @SnapshotHour + @SnapshotMin + '.snap'''')'' 
                              FROM ' + @ServerName + '.' + @DatabaseName + '.sys.database_files 
                              WHERE   type = 0 
                              AND state = 0' 
                 
                              --print @SQL2 
 
                              -- Try Catch block to execute SQL and handle errors    
                    BEGIN TRY 
                                    -- Insert files into tmp table  
                        EXEC ( @SQL2 
                            ) 
                    END TRY 
                    BEGIN CATCH 
             
                        SELECT  @DatabaseName,  
                                                message_id, 
                                severity, 
                                [text], 
                                @SQL 
                        FROM    sys.messages 
                                    WHERE   message_id = @@ERROR 
                        AND language_id = 1033 -- British English 
                    END CATCH 
         
                              -- Set Variables for the append , loop              
                    SELECT  @MinFileID = MIN(ID), 
                            @MaxFileID = MAX(ID) 
                    FROM    #SQL2 
 
                              -- Begin Loop to append , to the end of all except the last record 
                    WHILE @MinFileID < @MaxFileID 
                        BEGIN 
                                          -- Append , to the end of the current record 
                            UPDATE  #SQL2 
                            SET     SQL2 = SQL2 + ',' 
                            WHERE   ID = @MinFileID 
   
                                          -- Get the next DatabaseName ID 
                            SET @MinFileID = @MinFileID + 1   
                                    -- End Loop 
                        END 
 
                    SELECT  @MinFileID = MIN(ID), 
                            @MaxFileID = MAX(ID) 
                    FROM    #SQL2 
 
                    SET @SQL3 = '' 
   
                              -- Begin Loop to concatenante all files 
                    WHILE @MinFileID <= @MaxFileID 
                        BEGIN 
                                          -- Append , to the end of the current record 
                            SELECT  @SQL2 = SQL2 
                            FROM    #SQL2 
                            WHERE   ID = @MinFileID 
     
                            SET @SQL3 = @SQL3 + @SQL2 
   
                                          -- Get the next DatabaseName ID 
                            SET @MinFileID = @MinFileID + 1   
                 
                                    -- End Loop 
                        END 
 
                              -- Create End of SQL command to be run      
                    SET @SQL4 = 'AS SNAPSHOT OF ' + @DatabaseName + ';' 
   
                              -- Concatenate SQL variables ready for execution 
                    SET @SQL = @SQL1 + @SQL3 + @SQL4 
   
                              -- Try Catch block to execute SQL and handle errors    
                    BEGIN TRY 
                                    -- Create Database Snapshots 
                        EXEC ( @SQL 
                            ) 
                    END TRY 
                    BEGIN CATCH 
             
                        SELECT  @DatabaseName, 
                                                message_id, 
                                severity, 
                                [text], 
                                @SQL 
                        FROM    sys.messages 
                        WHERE   message_id = @@ERROR 
                                AND language_id = 1033 -- British English 
                    END CATCH 
         
                              -- Get the next DatabaseName ID 
                    SET @MinDBID = @MinDBID +
                        -- End Loop 
                END 
        END 
END 
 
Enjoy!

Chris

Comments

Leave a comment on the original post [sqlserver365.blogspot.com, opens in a new window]

Loading comments...