By David Wiseman (Administrator)Created 26 Mar 2012
My Rating:
Vote
Rating:
(2 votes)
Views:3248
Downloads:35
Source:

Backup Databases Script

Language:  T-SQL

Compatibility

SQL Server 7 No SQL Server 2000 No
SQL Server 2005 Yes SQL Server 2008 Yes
Description

SQL Server database backup script. Can be used to backup all databases on a SQL server instance or include/exclude specific databases. Backups can be automatically deleted after a specified period of time.


Notes
Requires SQL CLR IO Utility and dbo.SplitString function:
 
Code

Line Numbers: On  Off      Plain Text
CREATE PROC [dbo].[BackupDatabases](
	-- Databases to backup in a comma-separated list.
	-- e.g. database1,database2,database3
	-- NULL = Backup all databases
	@Databases NVARCHAR(MAX)=NULL,
	-- Databases to be excluded from the backup in a comma-separated list
	-- e.g. database1,database2,database3
	-- NULL = Don't exclude any databases
	@ExcludedDatabases NVARCHAR(MAX)=NULL,
	-- Type of Backup to perform
	-- FULL = full database backup, DIFF = differential database Backup, TRAN = transaaction log backup
	@BackupType CHAR(4)='FULL',
	-- Backup Directory
	-- e.g. \\BackupServer\BackupShare or C:\Backups
	-- Multiple locations can be specified using a pipe symbol.
	-- e.g. C:\Backups|D:\Backups|E:\Backups
	-- Note: If you just want to split the backup into multiple parts, you can also specify the same location multiple times. e.g.
	-- C:\Backups|C:\Backups|C:\Backups
	-- When multiple locations are specifed, the file name is appended with the file number and number of files.  e.g. 1of3, 2of3, 3of3
	@BackupDir NVARCHAR(MAX),
	-- Run VERIFYONLY check after backup has completed
	-- 1 = Verify, 0 = Don't verify
	@Verify BIT=0,
	-- Perform backup with CHECKSUM option
	-- 1 = Perform CHECKSUM, 0 = Don't Perform CHECKSUM
	@CheckSum BIT=0,
	-- Option to perform DBCC CHECKDB command before backup
	-- 0 = don't perform DBCC check, 1 = perform DBCC check, 2 = perform DBCC check with physical_only option
	@PerformDBCC TINYINT=0,
	-- Option to remove backup files after a specified number of hours
	-- e.g. 24 = Keep backups for 1 day, 168 = Keep backups for 7 days (24*7)
	-- NULL = Don't remove backup files
	@RetainHours INT=NULL,
	-- Option to delete old backup files before performing backup. 
	-- Ideally you want to ensure that you have a valid backup before deleting old backup files so this option is best set to zero.
	-- 0 = remove after backup completed, 1 = remove before backup completed, NULL = Backup files not removed
	@DeleteBeforeBackup BIT=0,
	-- Option to debug this stored procedure
	-- 1 = Debug Mode (Print Commands), 0 = Execute Mode (Perform Backups)
	@Debug BIT=0
)
AS
/* 
	Created By:		David Wiseman
	Date:			2009-12-01
	Website:		http://www.wisesoft.co.uk
	Description:
	SQL Server database backup script.  Can be used to backup all databases on a SQL server instance or include/exclude specific databases.  
	Backups can be automatically deleted after a specified period of time.
	 
	Examples:
	EXEC dbo.BackupDatabases @BackupDir='C:\Backups'
	EXEC dbo.BackupDatabases @BackupDir='C:\Backups',@RetainHours=336 -- Delete after 2 weeks
	EXEC dbo.BackupDatabases @BackupDir='C:\Backups',@RetainHours=336, -- Delete after 2 weeks
							@ExcludedDatabases='nobackupdb1,nobackupdb2',
							@BackupType='DIFF'  							
	Requires SQL CLR IO Utility and dbo.SplitString function:
	http://www.wisesoft.co.uk/articles/sql_server_clr_io_utility.aspx 
	http://www.wisesoft.co.uk/scripts/t-sql_cte_split_string_function.aspx														
*/
SET NOCOUNT ON;
DECLARE @Database sysname;
DECLARE @FileNamePattern nvarchar(1024);
DECLARE @BackupCommand nvarchar(max);
DECLARE @BackupName nvarchar(max);
DECLARE @BackupDBs TABLE(name sysname);
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
DECLARE @ErrorCount INT;
DECLARE @BackupLocations NVARCHAR(MAX)
SET @ErrorCount = 0
-- Check that a valid backup type is specified
IF @BackupType NOT IN('FULL','DIFF','TRAN')
BEGIN;
	RAISERROR ('Invalid Backup Type Specified. Options: FULL,DIFF,TRAN',11,1);
	RETURN;
END;
IF @Databases IS NULL
BEGIN -- Backup of all databases required (excluding databases that are not applicable for backup type)
	INSERT INTO @BackupDBs(name)
	SELECT name
	FROM sys.databases db 
	WHERE source_database_id IS NULL -- Exclude database snapshots
		AND name <> 'tempdb' -- Exclude tempdb database
		AND [state] = 0 --ONLINE databases only
		AND is_in_standby=0 -- Exclude databases in standby mode
		AND NOT (@BackupType='DIFF' AND name='master') -- Exclude master DB for Diff backups
		AND NOT (@BackupType='TRAN' AND recovery_model_desc='SIMPLE'); -- Exclude "SIMPLE" databases from TRAN backups
END
ELSE
BEGIN;
	-- Parse CSV string of database into temp table
	INSERT INTO @BackupDBs(name)
	SELECT value 
	FROM dbo.fnSplitString(@Databases,',');
END;
IF @ExcludedDatabases IS NOT NULL
BEGIN;
	-- Remove any excluded databases from backup list
	DELETE B
	FROM @BackupDBs B
	WHERE EXISTS(SELECT *
				FROM dbo.fnSplitString(@ExcludedDatabases,',') excl
				WHERE excl.value = B.Name);
END;
DECLARE cBackup CURSOR FAST_FORWARD FOR
	SELECT name 
	FROM @BackupDBs;
	
OPEN cBackup;
FETCH NEXT FROM cBackup INTO @Database;
WHILE @@FETCH_STATUS = 0
BEGIN;
	-- Check that we can perform the selected backup type on the database
	IF NOT EXISTS(SELECT name
				FROM sys.databases db 
				WHERE name = @Database
					AND source_database_id IS NULL -- Exclude database snapshots
					AND name <> 'tempdb' -- Exclude tempdb database
					AND [state] = 0 --ONLINE databases only
					AND is_in_standby=0 -- Exclude databases in standby mode
					AND NOT (@BackupType='DIFF' AND name='master') -- Exclude master DB for Diff backups
					AND NOT (@BackupType='TRAN' AND recovery_model_desc='SIMPLE') -- Exclude "SIMPLE" databases from TRAN backups
			)
	BEGIN;
		-- Backup type isn't valid for the database
		-- e.g. Database doesn't exist, TRAN backup specified for a SIMPLE database etc.
		
		SELECT @ErrorMessage = 'BackupDatabases Error: WARNING: ' + @BackupType + ' backup is invalid for database "' + @Database + '".  Backup Skipped.',
			@ErrorSeverity=10,@ErrorState=1,
			@ErrorCount = @ErrorCount + 1;
		-- Log error (Severity of 10 won't cause agent job to fail)
		RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState) WITH LOG;
		-- Get next database and continue processing
		FETCH NEXT FROM cBackup INTO @Database;
		CONTINUE;
	END;
	IF @PerformDBCC = 1
	BEGIN; -- Perform a DBCC CHECKDB command before backup
		IF @Debug = 1
		BEGIN;
			PRINT 'DBCC CHECKDB (' + QUOTENAME(@Database,'''') + ') WITH NO_INFOMSGS';
		END;
		ELSE
		BEGIN;
			DBCC CHECKDB (@Database) WITH NO_INFOMSGS;
		END;
	END;
	ELSE IF @PerformDBCC = 2
	BEGIN -- Perform a DBCC CHECKDB (PHYSICAL_ONLY option) before backup
		IF @Debug = 1
		BEGIN;
			PRINT 'DBCC CHECKDB (' + QUOTENAME(@Database,'''') + ') WITH NO_INFOMSGS, PHYSICAL_ONLY';
		END;
		ELSE
		BEGIN;
			DBCC CHECKDB (@Database) WITH NO_INFOMSGS, PHYSICAL_ONLY;
		END;
	END
	-- FileName pattern (Excluding the date)
	-- e.g. SERVER.DATABASENAME.FULL.
	SET @FileNamePattern = REPLACE(@@SERVERNAME,'\','_')  + '.' + REPLACE(@Database,'.','_') 
					 + '.' + UPPER(@BackupType) + '.';
	-- Get label for backup
	SET @BackupName = @Database + '-' + @BackupType + ' Database Backup';
	
	SELECT @BackupLocations = STUFF((
		SELECT ',DISK=N' + QUOTENAME(value + '\' + @FileNamePattern + REPLACE(REPLACE(REPLACE(CONVERT(varchar,GETDATE(),120),'-',''),':',''),' ','') +
				-- If multiple locations are specified, 
				ISNULL('_[' + CAST(ROW_NUMBER() OVER(ORDER BY value) as NVARCHAR(MAX)) + 'of' + CAST(NULLIF(COUNT(*) OVER(),1) as NVARCHAR(MAX)) + ']','')
				+ '.BAK','''') + '
				'
		FROM dbo.fnSplitString(@BackupDir,'|')
		FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)')
		,1,1,'')
		
	IF @BackupType = 'FULL'
	BEGIN;
		SET @BackupCommand = N'BACKUP DATABASE ' + QUOTENAME(@Database) + N'
								TO ' + @BackupLocations + 
								'WITH Name=N' + QUOTENAME(@BackupName,'''');
	END;
	ELSE IF @BackupType = 'DIFF'
	BEGIN;
		SET @BackupCommand = N'BACKUP DATABASE ' + QUOTENAME(@Database) + N'
								TO ' + @BackupLocations + 
								'WITH DIFFERENTIAL,Name=N' + QUOTENAME(@BackupName,'''');
	END;
	ELSE IF @BackupType = 'TRAN'
	BEGIN;
			SET @BackupCommand = N'BACKUP LOG ' + QUOTENAME(@Database) + N'
								TO ' + @BackupLocations + 
								'WITH Name=N' + QUOTENAME(@BackupName,'''');
	END;
	IF @CheckSum = 1
	BEGIN;
		SET @BackupCommand = @BackupCommand + ',CHECKSUM';
	END;
	
	-- Removes old backups for this database if the @DeleteBeforeBackup is specified (Otherwise cleanup occurs after the backup)
	IF @DeleteBeforeBackup = 1 AND @RetainHours IS NOT NULL 
	BEGIN;
		BEGIN TRY
			EXEC dbo.BackupDatabases_Cleanup @BackupDir=@BackupDir,@FileNamePattern=@FileNamePattern,@RetainHours=@RetainHours,@Debug=@Debug;
		END TRY
		BEGIN CATCH
				
				SELECT @ErrorMessage = 'BackupDatabases_Cleanup Error:' + ERROR_MESSAGE(),
					@ErrorSeverity = 10,
					@ErrorState = 1,
					@ErrorCount = @ErrorCount + 1
				-- Log error (Severity of 10 won't cause agent job to fail)
				RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState) WITH LOG
		END CATCH
	END;
	
	IF @Debug=1
	BEGIN;
		PRINT @BackupCommand;
	END;
	ELSE
	BEGIN;
		BEGIN TRY
			-- Run backup
			EXEC sp_executesql @BackupCommand;
		END TRY
		BEGIN CATCH
			-- Re-raise error and continue processing without verify or removing backup files
			SELECT @ErrorMessage = 'BackupDatabases Error:' + ERROR_MESSAGE(),
					@ErrorSeverity = 10,
					@ErrorState = 1,
					@ErrorCount = @ErrorCount+1;
			-- Log error (Severity of 10 won't cause agent job to fail)	
			RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState) WITH LOG;
			FETCH NEXT FROM cBackup INTO @Database;
			CONTINUE;
		END CATCH
	END;
	
	IF @Verify=1
	BEGIN; -- Perform a verify on the backup if specified
		SET @BackupCommand = 'RESTORE VERIFYONLY FROM ' + @BackupLocations;
		IF @CheckSum=1
		BEGIN;
			SET  @BackupCommand = @BackupCommand + ' WITH CHECKSUM';
		END;
		IF @Debug=1
		BEGIN;
			PRINT @BackupCommand;
		END;
		ELSE
		BEGIN;
			BEGIN TRY
				-- Run verify
				EXEC sp_executesql @BackupCommand;
			END TRY
			BEGIN CATCH
				-- Re-raise error and continue processing without removing backup files
				SELECT @ErrorMessage = 'BackupDatabases Error:' + ERROR_MESSAGE(),
					@ErrorSeverity = 10,
					@ErrorState = 1,
					@ErrorCount = @ErrorCount + 1;
				-- Log error (Severity of 10 won't cause agent job to fail)
				RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState) WITH LOG;
				
				FETCH NEXT FROM cBackup INTO @Database;
				CONTINUE;
			END CATCH
		END;
	END;
	-- Removes old backups for this database
	IF @DeleteBeforeBackup = 0 AND @RetainHours IS NOT NULL
	BEGIN;
		BEGIN TRY
			EXEC dbo.BackupDatabases_Cleanup @BackupDir=@BackupDir,@FileNamePattern=@FileNamePattern,@RetainHours=@RetainHours,@Debug=@Debug;
		END TRY
		BEGIN CATCH
				SELECT @ErrorMessage = 'BackupDatabases_Cleanup Error:' + ERROR_MESSAGE(),
					@ErrorSeverity = 10,
					@ErrorState = 1,
					@ErrorCount =  @ErrorCount + 1;
				-- Log error (Severity of 10 won't cause agent job to fail)
				RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState) WITH LOG;
		END CATCH
	END;
	
	FETCH NEXT FROM cBackup INTO @Database;
END;
CLOSE cBackup;
DEALLOCATE cBackup;
IF @ErrorCount >0
BEGIN
	SELECT @ErrorMessage ='One or more errors occurred during the backup.  Please see the previous messages for details',
			@ErrorSeverity = 11,
			@ErrorState = 1;
	RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState)
END

GO
CREATE PROC [dbo].[BackupDatabases_Cleanup](
	-- Backup folder
	@BackupDir NVARCHAR(MAX),
	-- FileName pattern (Excluding the date)
	-- e.g. SERVER.DATABASENAME.FULL.
	@FileNamePattern NVARCHAR(1024),
	-- Number of hours to keep backup
	-- e.g. 24 = Keep backups for 1 day, 168 = Keep backups for 7 days (24*7)
	@RetainHours INT,
	-- Option to see what commands would be run without deleting any files.
	@Debug BIT = 0
)
AS
/* 
	Created By:		David Wiseman
	Date:			2009-12-01
	Website:		http://www.wisesoft.co.uk
	Description:
	Used by dbo.BackupDatabases stored procedure to cleanup old backup files	
	
	Requires SQL CLR IO Utility and dbo.SplitString function:
	http://www.wisesoft.co.uk/articles/sql_server_clr_io_utility.aspx 
	http://www.wisesoft.co.uk/scripts/t-sql_cte_split_string_function.aspx	
							
*/
IF @RetainHours IS NOT NULL
BEGIN -- Remove old backup files if specified
	DECLARE @BackupPath NVARCHAR(1024);
	DECLARE @BackupLocation NVARCHAR(1024)
	
	DECLARE cBackupLocations CURSOR FAST_FORWARD FOR
		SELECT DISTINCT value as BackupLocation
		FROM dbo.fnSplitString(@BackupDir,'|')
	
	OPEN cBackupLocations
	FETCH NEXT FROM cBackupLocations INTO @BackupLocation
	WHILE @@FETCH_STATUS = 0
	BEGIN
		DECLARE cBackupFiles CURSOR FAST_FORWARD FOR
			SELECT [Path]
			FROM dbo.SQLIO_fnGetFiles(@BackupLocation,'*.BAK',0) -- Get all files with the extension "BAK" from the backup directory
			WHERE [file_name] LIKE @FileNamePattern + '%' -- Only filenames with the same pattern. e.g. SERVER.DATABASENAME.FULL.
			AND modified_time_utc < DATEADD(HH,-@RetainHours,GETUTCDATE()); -- Only files older than @RetainHours hours are deleted
		OPEN cBackupFiles;
		FETCH NEXT FROM cBackupFiles INTO @BackupPath;
		WHILE @@FETCH_STATUS = 0
		BEGIN;
			IF @Debug=1
			BEGIN;
				PRINT 'EXEC dbo.SQLIO_DeleteFile @Path=' + QUOTENAME(@BackupPath,'''');
			END;
			ELSE
			BEGIN;
				-- Delete backup file
				EXEC dbo.SQLIO_DeleteFile @Path=@BackupPath;
			END;
			
			FETCH NEXT FROM cBackupFiles INTO @BackupPath;
		END;
		CLOSE cBackupFiles;
		DEALLOCATE cBackupFiles;
		FETCH NEXT FROM cBackupLocations INTO @BackupLocation
	END
	CLOSE cBackupLocations
	DEALLOCATE cBackupLocations
END;
GO

 


Got a useful script? Click here to upload!


 

  Post Comment
Order By:  
User Comments
      
Be the first to post a comment!