http://www.sqlservercentral.com/blogs/cleveland-dba/2012/01/30/ryo-maintenance-plan-database-backups-part-2/

Printed 2014/12/20 08:18AM

RYO Maintenance Plan – Database Backups, Part 2

2012/01/30

Last time I dealt with the creation of subdirectories, in one or more root locations, to house my database backups. Today we’ll get into the meat of the database backup procedure itself. If you’ll recall, this procedure has to meet some specific criteria:

  1. It will need a minimum of tweaking to implement and maintain.
  2. It needs to be able to handle a single or multiple backup directories.
  3. It will need to create sub-directories for each database as needed, just as a normal maintenance plan would.
  4. It will handle both user and system databases.
  5. It will backup one or many databases, as dictated at run-time.
  6. It will handle full, differential, and transaction log backups.

Requirement 3 was met by the sp_create_backupdir procedure. With the help of that procedure and the BackupPaths table I created, today’s backup procedure will meet the rest. Without further ado, let’s jump right in.

Parameters and Variables

This new procedure, sp_backup_database, needs to be able to backup one database, many databases, even all of my databases. With that in mind, I’m using the same method for passing in the database name, the user can pass in a specific name or use wildcards to cover multiple databases. I’m using ‘%’ as the default, since more often than not I’ll be using this script to backup all of my databases.

My procedure also needs to be able to perform full, differential, and transaction log backups. I didn’t want to have a separate procedure for each type. So my second parameter defines the backup type, ‘F’ for full, ‘D’ for differential, and ‘L’ for log backups. The default is a full backup.

 ALTER PROCEDURE [maint].[sp_backup_database] (@dbs VARCHAR(128) = '%', @backup_type char(1) = 'F')
AS
BEGIN
	DECLARE

		 @path VARCHAR(256)		-- path for backup files
		,@pathnum tinyint		-- the path ID for backup files
		,@pathcount tinyint		-- number of backup paths
		,@subdir VARCHAR(256)	-- path for backup files
		,@fileName VARCHAR(256) -- filename for backup
		,@fileDate CHAR(8)		-- used for file name
		,@fileTime char(4)		-- used for file name
		,@extension VARCHAR(10) -- used for file name
		,@dbname varchar(128)
		,@dbid smallint
		,@sqlstmt nvarchar(max)
		,@diff_dbid smallint	-- we set this to 4 for diff backups to omit system databases
		,@trn_rm tinyint		-- we set this to 3 for transaction log backups to omit SIMPLE mode dbs
	 

I want to take a moment and point out a couple of the key variables I’ve defined here. The first is @diff_dbid. It’s not possible to perform a differential backup on the master database. I also didn’t think it necessary to perform differentials on any of the other system databases. So if the backup type is ‘D’, I’ll set this variable to 4, which is the maximum database ID for system databases. Otherwise, I’ll set it to 0. I’ll then use this in my query to build my database list. Similarly, I can’t do a transaction log backup on databases in simple recovery mode. So if my backup type is ‘L’, I’ll set @trn_rm to 3, since the recovery model code for simple mode is 3 in sys.databases. For any other backup type, I’ll set this value to 4, which should cover all recovery model types. If this doesn’t make sense now, it will soon.

With that said, my next step is to initialize my variables.

		-- Initialize our variables
		SELECT	 @fileDate = CONVERT(CHAR(8),GETDATE(),112)
				,@fileTime = REPLACE(CONVERT(varchar(5), GETDATE(), 108) , ':', '')
				,@extension = CASE	@backup_type
									WHEN 'F' THEN '.bak'
									WHEN 'D' THEN '.diff'
									WHEN 'L' THEN '.trn'
									ELSE '.unknown' END
				,@pathcount = COUNT(PathID)
				,@diff_dbid = CASE @backup_type WHEN 'D' THEN 4 ELSE 0 END
				,@trn_rm = CASE @backup_type WHEN 'L' THEN 3 ELSE 4 END
		FROM maint.BackupPaths 

Building the backup statement

I’m ready to start building backup statements. I tried to avoid using cursors whenever possible, but here was another spot where I simply couldn’t find a way to avoid it. So I built a cursor for all online databases (state=0) other than tempdb, matching the name or wildcard I passed in. Here’s where those two variables also come into play, I’m only including databases with a database_id > @diff_dbid (4 to omit system databases, 0 for everything) and those with a recovery model < @trn_rm (3 to omit SIMPLE mode databases, 4 to include everything). But before I do any of that, I need to call my sp_create_backupdir so I have a place to put all my backup files.

 		-- Create the necessary subdirs in each backup path
		EXEC maint.sp_create_backupdir @dbs

		-- Build the backup statement(s)
		DECLARE getdbs CURSOR STATIC FOR
			SELECT d.database_id, d.name FROM sys.databases d
			WHERE d.name <> 'tempdb'
			AND d.state = 0
			AND d.name LIKE @dbs
			AND d.database_id > @diff_dbid		--no system dbs for diff backups
			AND d.recovery_model < @trn_rm		--no simple mode dbs for log backups 

Handling multiple files

Inside that getdbs cursor loop, I build my backup statements. Now remember, I need to accomodate backing up to multiple files, so within that outer cursor I’ll also need another, inner cursor to handle the DISK clause. As I loop through that inner cursor, I build the path and name of the backup file, including the date and time of the backup. To help me determine later (if a restore is needed, for example) whether this backup contains multiple files or not, I also append a “1of2″ notation to my files. If there’s only one file, it’s “1of1″.
When I first started developing this procedure, all databases were to be backed up the same way. Then I started thinking about master. Master is small enough, there’s no real need to split it out to multiple files. Master is also extremely critical. Did I want to take a chance on one of those files not being backed up to tape? No. So I incorporated a little extra logic so that all system databases are only backed up in a single file to the first backup path.

 			IF @backup_type IN ('F', 'D')
				SET @sqlstmt = 'BACKUP DATABASE ['+@dbname+'] TO '
			ELSE
				SET @sqlstmt = 'BACKUP LOG ['+@dbname+'] TO '

			DECLARE backupfiles CURSOR FOR
				SELECT b.PathID,b.BackupPath
				FROM sys.databases d
				CROSS JOIN maint.BackupPaths b
				WHERE d.name = @dbname
				ORDER BY d.name, b.PathID
			OPEN backupfiles
			FETCH backupfiles INTO @pathnum, @subdir

			-- build the file clause section
			WHILE @@FETCH_STATUS = 0
			BEGIN
				SET @subdir = @subdir+'\'+@dbname

				SET @fileName = @dbname+'_'+@fileDate+'_'+@fileTime+'_'
						+CAST(@pathnum AS VARCHAR)+'of'

				IF @dbid <= 4
					SET @fileName = @fileName+CAST(@pathnum AS VARCHAR)+@extension
				ELSE
					SET @fileName = @fileName+CAST(@pathcount AS VARCHAR)+@extension

				SET @sqlstmt = @sqlstmt + 'DISK = ''' +@subdir+'\'+@fileName+''''

				IF @pathnum < @pathcount and @dbid > 4
					SET @sqlstmt = @sqlstmt + ', '

				IF @dbid <= 4
					BREAK;

				FETCH backupfiles INTO @pathnum, @subdir
			END
			CLOSE backupfiles
			DEALLOCATE backupfiles
		 

INIT and COMPRESSION

Finally, a few last options I wanted to set. First of all, I wanted to overwrite any existing backup with that name. That’s an option I always choose when doing backups. Secondly, if this is running on a SQL 2008 instance, I want to use backup compression. Chances are, if I’m backing up to multiple drives, these databases are pretty big to begin with, so why not same some space and time by compressing them? And the last thing I do is append the DIFFERENTIAL clause, if this is a differential backup.

 			-- Overwrite the backup file if it exists
			SET @sqlstmt = @sqlstmt + ' WITH INIT'

			-- Use backup compression in 2008+
			IF SUBSTRING(CAST(SERVERPROPERTY('ProductVersion') AS varchar),1, CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS varchar))-1) > 9
				SET @sqlstmt = @sqlstmt + ', COMPRESSION'

			-- Is this a differential backup?
			IF @backup_type = 'D'
				SET @sqlstmt = @sqlstmt + ', DIFFERENTIAL'

			EXEC (@sqlstmt)
			--PRINT @sqlstmt
			FETCH getdbs INTO @dbid, @dbname 

Final Procedure

As with the sp_create_backupdir procedure, my last step is to add in some error handling and I’m done. Here’s the final result.

 SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [maint].[sp_backup_database] (@dbs VARCHAR(128) = '%', @backup_type char(1) = 'F')
AS
BEGIN
	DECLARE

		 @path VARCHAR(256)		-- path for backup files
		,@pathnum tinyint		-- the path ID for backup files
		,@pathcount tinyint		-- number of backup paths
		,@subdir VARCHAR(256)	-- path for backup files
		,@fileName VARCHAR(256) -- filename for backup
		,@fileDate CHAR(8)		-- used for file name
		,@fileTime char(4)		-- used for file name
		,@extension VARCHAR(10) -- used for file name
		,@dbname varchar(128)
		,@dbid smallint
		,@sqlstmt nvarchar(max)
		,@diff_dbid smallint	-- we set this to 4 for diff backups to omit system databases
		,@trn_rm tinyint		-- we set this to 3 for transaction log backups to omit SIMPLE mode dbs

	SET NOCOUNT ON

	BEGIN TRY

		IF @backup_type NOT IN ('F', 'D', 'L')
			RAISERROR (
				 N'Invalid backup type.  Please specify F (full), D (differential) or L (Log).'
				,16
				,1
				);

		-- Initialize our variables
		SELECT	 @fileDate = CONVERT(CHAR(8),GETDATE(),112)
				,@fileTime = REPLACE(CONVERT(varchar(5), GETDATE(), 108) , ':', '')
				,@extension = CASE	@backup_type
									WHEN 'F' THEN '.bak'
									WHEN 'D' THEN '.diff'
									WHEN 'L' THEN '.trn'
									ELSE '.unknown' END
				,@pathcount = COUNT(PathID)
				,@diff_dbid = CASE @backup_type WHEN 'D' THEN 4 ELSE 0 END
				,@trn_rm = CASE @backup_type WHEN 'L' THEN 3 ELSE 4 END
		FROM maint.BackupPaths

		-- Create the necessary subdirs in each backup path
		EXEC maint.sp_create_backupdir @dbs

		-- Build the backup statement(s)
		DECLARE getdbs CURSOR STATIC FOR
			SELECT d.database_id, d.name FROM sys.databases d
			WHERE d.name <> 'tempdb'
			AND d.state = 0
			AND d.name LIKE @dbs
			AND d.database_id > @diff_dbid		--no system dbs for diff backups
			AND d.recovery_model < @trn_rm		--no simple mode dbs for log backups
		OPEN getdbs
		FETCH getdbs INTO @dbid, @dbname
		WHILE @@FETCH_STATUS = 0
		BEGIN

			IF @backup_type IN ('F', 'D')
				SET @sqlstmt = 'BACKUP DATABASE ['+@dbname+'] TO '
			ELSE
				SET @sqlstmt = 'BACKUP LOG ['+@dbname+'] TO '

			DECLARE backupfiles CURSOR FOR
				SELECT b.PathID,b.BackupPath
				FROM sys.databases d
				CROSS JOIN maint.BackupPaths b
				WHERE d.name = @dbname
				ORDER BY d.name, b.PathID
			OPEN backupfiles
			FETCH backupfiles INTO @pathnum, @subdir

			-- build the file clause section
			WHILE @@FETCH_STATUS = 0
			BEGIN
				SET @subdir = @subdir+'\'+@dbname

				SET @fileName = @dbname+'_'+@fileDate+'_'+@fileTime+'_'
						+CAST(@pathnum AS VARCHAR)+'of'

				IF @dbid <= 4
					SET @fileName = @fileName+CAST(@pathnum AS VARCHAR)+@extension
				ELSE
					SET @fileName = @fileName+CAST(@pathcount AS VARCHAR)+@extension

				SET @sqlstmt = @sqlstmt + 'DISK = ''' +@subdir+'\'+@fileName+''''

				IF @pathnum < @pathcount and @dbid > 4
					SET @sqlstmt = @sqlstmt + ', '

				IF @dbid <= 4
					BREAK;

				FETCH backupfiles INTO @pathnum, @subdir
			END
			CLOSE backupfiles
			DEALLOCATE backupfiles

			-- Overwrite the backup file if it exists
			SET @sqlstmt = @sqlstmt + ' WITH INIT'

			-- Use backup compression in 2008+
			IF SUBSTRING(CAST(SERVERPROPERTY('ProductVersion') AS varchar),1, CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS varchar))-1) > 9
				SET @sqlstmt = @sqlstmt + ', COMPRESSION'

			-- Is this a differential backup?
			IF @backup_type = 'D'
				SET @sqlstmt = @sqlstmt + ', DIFFERENTIAL'

			EXEC (@sqlstmt)
			--PRINT @sqlstmt
			FETCH getdbs INTO @dbid, @dbname
		END
		CLOSE getdbs
		DEALLOCATE getdbs

	END TRY
	BEGIN CATCH

		DECLARE @ErrorMessage NVARCHAR(4000);
		DECLARE @ErrorSeverity INT;
		DECLARE @ErrorState INT;

		SELECT @ErrorMessage = ERROR_MESSAGE(),
			   @ErrorSeverity = ERROR_SEVERITY(),
			   @ErrorState = ERROR_STATE();

		RAISERROR (@ErrorMessage, -- Message text.
				   @ErrorSeverity, -- Severity.
				   @ErrorState -- State.
				   );

	END CATCH

END
GO 

With that, I have a backup procedure that meets all my requirements. The procedures themselves won’t require any modification based on the system, all I’ll need to do is add the proper location(s) to BackupPaths. It can handle one or more backup directories. It handles one or many databases, as well as system databases. And it does any type of backup.

Next step: Cleanup

Now, any good backup routine will also include some sort of cleanup. We’ll cover that next time.


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.