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; 
