Technical Article

Backup All User Databases

,

It's about time I gave something back to the comunity that has helped me over the years.  I built this script after inheriting a SQL 2005 Server in November of last year.  The previous person responsible for the server (I can't call them a DBA considering everything I have discovered) was relying on Maintneance plans and manually triming the backup files.  Not a bad solution, but it required a lot of manual interventions and did not support database additions or deletions.
In addition I discovered that new databases are created on a semi-regular basis, but noone thinks to notify IT that a new DB has been created.  So I created this script.  It will backup all user databases on a SQL server.
Based on recovery model and time of day either a full or t-log
backup of the user databases will be performed.  System, 
replication, and reporting databases will be excluded.
The script is designed to be run hourly.
Full Backups are run on Sunday at 12 AM (Midnight)
The intervals regarding DIFFERENTIAL and LOG backup 
can be changed to suit your needs.
Set @Backupshare to the root path of your backup files
EXAMPLE: 
  Local Disk:     C:\SQLBackup\
  Network Share:  \\<MachineName>\<ShareName>\
The script will create subfolders for the machine, instance, and each database.
EXAMPLE:
  @BackupShare + <MachineName>\<InstanceName>\<DatabaseName>
Every Sunday at Midnight the backup devices will be recreated with new physical files and a date stamp.  The previous files willnot be deleted. (This is intentional to allow for your own retention policy)
The script utilizes xp_dirtree and xp_create_subdir to ensure the backup folder is in place.
References:
http://www.mssqltips.com/sqlservertip/1460/sql-server-script-to-create-windows-directories/
http://www.patrickkeisler.com/2012/11/how-to-use-xpdirtree-to-list-all-files.html
http://www.kodyaz.com/articles/create-folder-sql-sys-xp_create_subdir-extended-stored-procedure.aspx
To execute this script the user must have full control permissions to the backup share.
Please feel free to modify this script to suit your needs.
You're feedback is welcomed and appreciated.
SET NOCOUNT ON;

DECLARE
   @Dayofweek int ,
   @Hour int ,
   @Backupfiledatestring varchar( 8 ) ,
   @Sql nvarchar( 2000 ) ,
   @DatabaseName varchar( 128 ) ,
   @Recoverymodel varchar( 50 ) ,
   @Backupdevicename varchar( 50 ) ,
   @Backupphysicalname varchar( 128 ),
   @Backupshare varchar( 100 ) ,
   @Backuppath varchar( 100 ) ,
   @BackupDBpath varchar( 100 ) ,
   @Machinename varchar( 50 ) ,
   @Instancename varchar( 50 ) ,
   @Isxpcmdshellenabled int;

IF OBJECT_ID( 'tempdb..#dirtree' ) IS NOT NULL
    BEGIN
        DROP TABLE #dirtree
    END;
CREATE TABLE #dirtree ( dirName varchar( 255 ) ,
                        depth int );

/* check xp_cmdshell is enabled */SELECT @Isxpcmdshellenabled = CONVERT( int , ISNULL( value , value_in_use ))
FROM master.sys.configurations
WHERE  name = 'xp_cmdshell';

/* enable xp_cmdshell is it is not */IF @Isxpcmdshellenabled != 1
    BEGIN
        EXEC sp_configure 'xp_cmdshell' , 1;
        RECONFIGURE;
    END;

/* set the backup folders parameters */SELECT @Machinename = CONVERT( sysname , SERVERPROPERTY( 'MachineName' )) ,
       @Instancename = CONVERT( sysname , SERVERPROPERTY( 'InstanceName' )) ,
       @Backupshare = '\\BackupServer\BackupShare\' ,
       @Dayofweek = DATEPART( dw , GETDATE( )) ,
       @Hour = DATEPART( HOUR , GETDATE( )) ,
       @Backupfiledatestring = CONVERT( varchar( 8 ) , DATEADD( DAY , - ( DATEPART( DW , GETDATE( )) - 1 ) , GETDATE( )) , 112 );

/* verify the path exists for the backup devices */-- 1. check the root folder for the server exists
DELETE #dirtree;
SET @Backuppath = @Backupshare;
INSERT INTO #dirtree ( dirName ,
                       depth )
EXEC master.sys.xp_dirtree @Backuppath , 1;

SET @Backuppath = @Backupshare + @Machinename;
IF NOT EXISTS ( SELECT *
                FROM #dirtree
                WHERE
                       dirname = @Machinename
                   AND depth = 1 )
    BEGIN
        PRINT 'Creating machine folder';
        EXEC master.sys.xp_create_subdir @Backuppath;
    END;

-- 2. create instance folder if it does not exist
DELETE #dirtree;
INSERT INTO #dirtree ( dirName ,
                       depth )
EXEC master.sys.xp_dirtree @Backuppath , 1;

SET @Backuppath = @Backuppath + '\' + @Instancename;
IF NOT EXISTS ( SELECT *
                FROM #dirtree
                WHERE
                       dirname = @Instancename
                   AND depth = 1 )
    BEGIN
        PRINT 'Creating instance folder';
        EXEC master.sys.xp_create_subdir @Backuppath;
    END;

/* loop thru the user databases */DECLARE databaseList CURSOR
    FOR
        SELECT d.name ,
               recovery_model_desc
        FROM master.sys.databases d
        WHERE
               d.name NOT IN ( 'master' , 'model' , 'msdb' , 'tempdb' , 'distribution' )
           AND d.name NOT LIKE 'ReportServer%'
        ORDER BY d.name;

OPEN databaseList;
FETCH NEXT FROM databaselist INTO @DatabaseName , @Recoverymodel;
WHILE @@Fetch_Status = 0
    BEGIN
          PRINT ''
          PRINT @DatabaseName
          -- create database folder if it does not exist
          DELETE #dirtree;
          INSERT INTO #dirtree ( dirName ,
                                 depth )
          EXEC master.sys.xp_dirtree @Backuppath , 1;

          SET @BackupDBpath = @Backuppath + '\' + @Databasename;
          IF NOT EXISTS ( SELECT *
                          FROM #dirtree
                          WHERE
                                 dirname = @Databasename
                             AND depth = 1 )
              BEGIN
                  PRINT 'Creating database folder';
                  EXEC master.sys.xp_create_subdir @BackupDBpath;
              END;

        /* check for SQL Backup Devices */        SET @Backupdevicename = @DatabaseName + ' Backup Device';
        SET @Backupphysicalname = @BackupDBpath + '\' + @Backupdevicename + '_' + @Backupfiledatestring + '.bak';

        /* Initialize the backup if Day = Sunday and Hour = 12 AM or Backup device does not exist */        IF
               @Dayofweek = 1
           AND @Hour = 0
            OR NOT EXISTS ( SELECT name
                            FROM master.dbo.sysdevices
                            WHERE name = @Backupdevicename )
            BEGIN
                PRINT 'Initializing backup device'
                IF  EXISTS ( SELECT name
                             FROM master.dbo.sysdevices
                             WHERE name = @Backupdevicename )
                    BEGIN
                        EXEC master.dbo.sp_dropdevice @Logicalname = @Backupdevicename;
                    END;

                EXEC master.dbo.sp_addumpdevice
                @Devtype = N'disk' ,
                @Logicalname = @Backupdevicename ,
                @Physicalname = @Backupphysicalname;

                SET @Sql = 'BACKUP DATABASE ' + @DatabaseName + ' TO  [' + @Backupdevicename + '] WITH  NOFORMAT, INIT, NAME = N''' + @DatabaseName + '-Full Database Backup'', STATS = 10;';

            END;
        ELSE
            BEGIN

                /* perform a differential backup every 4 hours between 8 AM and 9 PM */                IF
                       @Hour % 4 = 0
                   AND @Hour BETWEEN 8 AND 21
                    BEGIN
                        SET @Sql = 'BACKUP DATABASE ' + @DatabaseName + ' TO  [' + @Backupdevicename + '] WITH  NOFORMAT, NOINIT, NAME = N''' + @DatabaseName + '-Differential Database Backup'', STATS = 10, DIFFERENTIAL;';
                    END;

                /* backup t-logs for DBs in FULL recovery every hour between 8 AM and 7 PM */                IF
                       @Recoverymodel = 'FULL'
                   AND @Hour % 4 != 0
                   AND @Hour BETWEEN 8 AND 19
                    BEGIN
                        SET @Sql = 'BACKUP LOG  ' + @DatabaseName + ' TO  [' + @Backupdevicename + '] WITH  NOFORMAT, NOINIT, NAME = N''' + @DatabaseName + '-Transaction Log Backup'', STATS = 10;';
                    END;
            END;

        /* debugging code */        PRINT 'Executing backup command';
        PRINT @SQL;
        PRINT ''

        EXEC sp_executesql @Sql;
        SET @SQL = ''
        FETCH NEXT FROM databaselist INTO @DatabaseName , @Recoverymodel;
    END;

CLOSE databaseList;
DEALLOCATE databaseList;

/* disbale xp_cmdshell is we enabled it */IF @Isxpcmdshellenabled != 1
    BEGIN
        EXEC sp_configure 'xp_cmdshell' , 0;
        RECONFIGURE;
    END;

IF OBJECT_ID( 'tempdb..#dirtree' ) IS NOT NULL
    BEGIN
        DROP TABLE #dirtree
    END;

Rate

2 (1)

You rated this post out of 5. Change rating

Share

Share

Rate

2 (1)

You rated this post out of 5. Change rating