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

Rate

Share

Share

Rate