Create Database Script

I always try to enforce standards across all the SQL Servers I manage, it makes tons of administrative tasks much easier to complete and ensures consistency across the estate.  One thing that really irritates me is existing “legacy” environments that do not conform to these standards.  For example I have server X which is running SQL Server 2005 on Windows Server 2003, this server was installed and configured long before I started working for my employer and has a disk configuration of;

C:\ - OS

D:\ - SQL Server files

With this non-standard drive configuration my standard create database script falls flat on its face as the Data and Log directories are hard coded (not brilliant I know but with consistency it works a treat :)  As my Standard installation guide specifies the Data and Log directory paths I know that any new servers will be consistent but the new script will also work for existing “legacy” servers with non-standard drive configurations.

Below is the modified script, It will create a database called dbLogging in the default data and log directories with a few other settings, which may or may not be what you require so get modding.  Yes it does assume that the database xabcx123xdbxdefaultxfilexpathx321xcbax does not exist, to be honest I don’t really care, if someone has a database with that name then I would love to know why.  If you do then you just need to do a find and replace to something more unconventional like say dbAdmin or dbSQLAdmin.

/*

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

      Create database with files in default the directories

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

   

      For more SQL resources, check out SQLServer365.blogspot.co.uk

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

      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"

    

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

*/

-- set database context

USE master;

GO

-- Create a temp database

CREATE DATABASE xabcx123xdbxdefaultxfilexpathx321xcbax;

-- Declare variables

DECLARE @DefaultDataFilePath VARCHAR(512)

DECLARE @DefaultLogFilePath VARCHAR(512)

DECLARE @DatabaseName VARCHAR(512)

DECLARE @DataFileName VARCHAR(517)

DECLARE @LogFileName VARCHAR(517)

DECLARE @DataFileExtension VARCHAR(4)

DECLARE @LogFileExtension VARCHAR(4)

DECLARE @SQL VARCHAR(4000)

/*

      *** THIS IS ALL YOU NEED TO SPECIFY ***

*/

SET @DatabaseName = 'dbLogging'

-- Set variables

SET @DataFileName = @DatabaseName + '_Data'

SET @LogFileName = @DatabaseName + '_Log'

SET @DataFileExtension = '.mdf'

SET @LogFileExtension = '.ldf'

-- Get the default data path  

SELECT @DefaultDataFilePath =   

(   SELECT LEFT(physical_name,LEN(physical_name)-CHARINDEX('\',REVERSE(physical_name))+1)

    FROM sys.master_files mf  

    INNER JOIN sys.databases d  

    ON mf.database_id = d.database_id  

    WHERE d.[name] = 'xabcx123xdbxdefaultxfilexpathx321xcbax' AND type = 0);

-- Get the default log path  

SELECT @DefaultLogFilePath =   

(   SELECT LEFT(physical_name,LEN(physical_name)-CHARINDEX('\',REVERSE(physical_name))+1)  

    FROM sys.master_files mf  

    INNER JOIN sys.databases d  

    ON mf.database_id = d.database_id

    WHERE d.[name] = 'xabcx123xdbxdefaultxfilexpathx321xcbax' AND type = 1);

-- Drop the temp database

IF EXISTS(SELECT 1 FROM master.sys.databases WHERE [name] = 'xabcx123xdbxdefaultxfilexpathx321xcbax' )  

BEGIN 

    DROP DATABASE xabcx123xdbxdefaultxfilexpathx321xcbax  

END;

-- If the database already exists print message tro client

IF EXISTS(SELECT 1 FROM master.sys.databases WHERE [name] = @DatabaseName) 

BEGIN

      -- Print message if database already exists

      PRINT 'Database ' + @DatabaseName + ' already exists on ' + @@SERVERNAME

END;

-- Build up SQL string to create database

IF NOT EXISTS(SELECT 1 FROM master.sys.databases WHERE [name] = @DatabaseName)  

BEGIN

      SET @SQL = 'CREATE DATABASE ' + '[' + @DatabaseName + ']' +'

      ON

      ( NAME = ' + '[' + @DataFileName + ']' + ',

            FILENAME = '''+ @DefaultDataFilePath + @DataFileName + @DataFileExtension + ''',

            SIZE = 1024MB,

            MAXSIZE = UNLIMITED,

            FILEGROWTH = 10% )

      LOG ON

      ( NAME = ' + '[' + @LogFileName + ']' + ',

            FILENAME = '''+ @DefaultLogFilePath + @LogFileName + @LogFileExtension + ''',

            SIZE = 1024MB,

            MAXSIZE = UNLIMITED,

            FILEGROWTH = 10% )'

-- Create the database

--Try Catch block to create database                 

      BEGIN TRY

            EXEC ( @SQL )

            PRINT @DatabaseName + ' has been created on ' + @@SERVERNAME

      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

END;

GO

Enjoy!

Chris

A Script A Day - Day 27 - Missing Database Backups

Today’s script is one I have used many times in the past to check for missing backups.  The script will return the database name and the last full backup date of all databases that are in the FULL recovery model and have not had a FULL database backup in the last 24 Hours.

/*

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

      Missing Database Backups

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

     

      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"

     

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

*/

-- Change database context

USE msdb;

GO

-- Create temporary table for database names

IF OBJECT_ID('tempDB.dbo.#Database') IS NOT NULL

      DROP TABLE #Database ;

CREATE TABLE #Database

(

      ID INT IDENTITY (1,1),

      DatabaseName VARCHAR(255)

);

GO

-- Declare variables

DECLARE @Date DATETIME

-- Set variables

SET @Date = GETDATE()-1

-- Get database in FULL recovery WITH a FULL backup in the last 24 hours

INSERT INTO #Database

SELECT DISTINCT

      database_name

FROM

      msdb.dbo.backupset

WHERE

      recovery_model = 'FULL'

      AND [type] = 'D'

      AND backup_finish_date > @Date;

-- Get databases in FULL recovery without a FULL backup in the last 24 hours

SELECT

      b.database_name AS DatabaseName,

      MAX(b.backup_finish_date) AS LastFullBackup

FROM

      msdb.dbo.backupset b

WHERE

      b.database_name NOT IN (SELECT DatabaseName FROM #Database)

      AND b.recovery_model = 'FULL'

      AND b.[type] = 'D'

      AND b.backup_finish_date < @Date

GROUP BY b.database_name

ORDER BY b.database_name ASC;

GO

Enjoy!

Chris

A Script A Day - Day 26 - RESTART a Database Restore

Today’s script is another one based on database restores.  There is a little known clause of the RESTORE DATABASE command called RESTART.  The name of this clause is deceptive, it does not RESTART anything and should actually be called RESUME or CONTINUE.  What it allows you to do is to resume a restore that has failed for a reason other than a SQL Server issue (backup consistancy, lack of space etc).

Take the following example which is simulated by the script.  You start a large backup lets say 500GB restoring before you leave the office one evening which you know from previous restores takes about 4 hours to run.  Now being a DBA you will of course log on ;) to check the restore is succesfull and finish any other tasks required before the database is ready to be used.  You notice the database is in a RESTORING state but your query window with the restore command has errored, after a bit of digging you find that 3 hours and 50 minutes into the restore the server lost power and went down (assume this is a development environment with no UPS or fail over) and the IT operations team brought the server back online.

If you where to restore the database again then it would take another 4 hours to run but by using the RESTART clause the database restore will only take about another 10 minutes!

Now I haven’t used this in anger but have tested it quite a few times on SQL Server 2008 and SQL Server 2008 R2 and have never had a problem.

/*

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

      RESTART a Database Restore

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

     

      For more SQL resources, check out SQLServer365.blogspot.co.uk

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

      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"

     

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

*/

-- Change database context

USE SQLServer365;

GO

-- Get a list of database files and locations for the restore

sp_helpfile;

GO

-- Change database context

USE master;

GO

-- Backup database

BACKUP DATABASE SQLServer365 TO DISK = 'D:\SQL\Backup\SQLServer365_RESTART_Test.bak'

GO

--Restore Database

RESTORE DATABASE SQLServer365 FROM DISK = 'D:\SQL\Backup\SQLServer365_RESTART_Test.bak'

WITH REPLACE, MOVE 'SQLServer365_Data' TO 'D:\SQL\Data\SQLServer365_Data.mdf',

MOVE 'SQLServer365_Log' TO 'D:\SQL\Log\SQLServer365_Log.ldf';

GO

-- STOP SQL SERVER SERVICE WHILE RESTORING

-- Error Message Received on client

-- START SQL SERVER SERVICE

-- There will be a message in SQL log like the below

-- The database 'SQLServer365' is marked RESTORING and is in a state that does not allow recovery to be run.

-- At this point he database is marked as RESTORING and is inaccesible.

-- Change database context

USE master;

GO

-- Restore the database with the RESTART option (this resumes the restore, honestly!)

RESTORE DATABASE SQLServer365 FROM DISK = 'D:\SQL\Backup\SQLServer365_RESTART_Test.bak'

WITH REPLACE, RESTART, MOVE 'SQLServer365_Data' TO 'D:\SQL\Data\SQLServer365_Data.mdf',

MOVE 'SQLServer365_Log' TO 'D:\SQL\Log\SQLServer365_Log.ldf';

GO

-- Results

/*

Processed 18192 pages for database 'SQLServer365', file 'SQLServer365_Data' on file 1.

Processed 1 pages for database 'SQLServer365', file 'SQLServer365_Log' on file 1.

RESTORE DATABASE successfully processed 18193 pages in 19.852 seconds (7.159 MB/sec).

*/

Enjoy!

Chris

A Script A Day - Day 22 - Database Backup

Today’s script follows on from Day 16’s script which was about database restores.  In a backwards way the script is a simple backup database script with two backup commands.  For all my production servers I have custom maintenance routines which take care of transaction log and full backups along with a whole host of other maintenance tasks.  But for a one off backup lets say before decomissioning an old server or before running some test data scripts in development then the syntax to backup a database doesn’t get much easier!  This is yet another example of T-SQL being better than using the GUI as it is far quicker ;)

Now as with my script on Day 16 not everyone knows T-SQL and thus wouldn’t be able to backup a database using it.

So I’ve included two backup database commands one will backup the SQLServer365 database to disk and one will backup the SQLServer365 database to disk with compression.  Backup compression was introduced in SQL Server 2008, details can be found on Technet.  Basically backup compression will generally save you disk space, in the example here on my instance the backup without compression was 146MB the backup with compression was 10MB!

/*

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

      Backup Database

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

     

      For more SQL resources, check out SQLServer365.blogspot.co.uk

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

      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"

     

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

*/

-- Change database context

USE master;

GO

-- Backup database

BACKUP DATABASE SQLServer365

TO DISK = 'D:\SQL\Backup\SQLServer365_20120224.bak';

GO

-- Backup database with compression

BACKUP DATABASE SQLServer365

TO DISK = 'D:\SQL\Backup\SQLServer365_20120224_Compressed.bak'

WITH COMPRESSION;

GO

Enjoy!

Chris

A Script A Day - Day 16 - Database Restore

Today’s script is one I have used more times that I care to remember.  As a DBA database backups and restores are your bread and butter, they are second nature (or should be;).  By this I mean good old T-SQL not using SSMS, I can honestly say I have never backed up or restored a database using SSMS, I have used EM in SQL 2000 in my pre DBA days though.

So for all you DBA’s forgive the basic nature of the script but not everyone knows T-SQL and thus wouldn’t be able to restore a database using it.  See my Database 101 post on humility. 

For everyone else the script will restore the SQLServer365 database from a backup and will overwrite the existing files.  It uses RESTORE FILELISTONLY to get the files in the backup and sp_helpfile to get the existing file locations.  The LocicalName column values from RESTORE FILELISTONLY are used to specify what we are MOVEing and the filename column values from sp_helpfile are used to specify where we are MOVEing them TO.

/*

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

      Restore Database

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

     

      For more SQL resources, check out SQLServer365.blogspot.co.uk

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

      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"

     

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

*/

-- Set database to the user database

USE SQLServer365;

GO

-- Get file list from backup

RESTORE FILELISTONLY FROM DISK = 'D:\Backups\SQLServer365_20120222.bak';

GO

-- Return database file locations

EXEC dbo.sp_helpfile;

GO

-- Set database context to master

USE master;

GO

-- Restore the database

RESTORE DATABASE SQLServer365

FROM DISK = 'D:\Backups\SQLServer365_20120222.bak'

WITH REPLACE, MOVE 'SQLServer365_Data' TO 'D:\SQL\Data\SQLServer365_Data.mdf', MOVE 'SQLServer365_Log' TO 'D:\SQL\Log\SQLServer365_Log.ldf';

GO

Enjoy!

Chris

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.co.uk

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

      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 + 1 

                        -- End Loop 

                END 

        END 

END 

 

Enjoy!

Chris