RYO Maintenance Plan – Database Backups, Part 3


4 out of 5 DBAs agree

I’ve covered creating my backup directories and the actual backup of the database(s). The last thing I want to do in my process is clean up any old backup files. Once again, I want to allow for some flexibility in my procedure. Much like the maintenance plan task I’m replacing, my procedure should be able to handle different file extensions and a range of time intervals. For example, I should be able to delete .bak files more than 2 days old and .trn files more than 7 days old, etc. The only real difference in this procedure vs the plan task is that I’m not specifying the path in my procedure. It’s going to get that from my BackupPath table.

xp_delete_file

In this particular procedure, I’m going to use another one of those undocumented stored procedures: xp_delete_file. Xp_delete_file is what’s actually used behind the scenes by the maintenance cleanup task. Here’s the syntax:

 EXECUTE master.dbo.xp_delete_file
	[file type] -- 0 for backup files, 1 for maintenance plan report files
	,[directory]
	,[file extension]
	,[delete date] -- a character string representing your delete date
	,[subdir flag] -- 0 = do not include subdirectories, 1 = include subdirs 

For example, the following statement would delete all backup files with a “bak” extension in the D:\Backup1 directory, and its subfolders, that were made before January 25 at 11am.

 EXEC master.dbo.xp_delete_file 0,N'D:\Backup1',N'bak','2012-01-25T11:00:00',1 

This is such a straightforward procedure, let’s jump right in. My procedure will accept 3 parameters: the first two, @period and @unit go together. They’re what determine our retention period. The last parameter, @fileext, is the file extension we’re cleaning up.

 USE master
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [maint].[sp_clean_backupdir]
	(@period smallint = 7, @unit char(1) = 'D', @fileext NVARCHAR(4) = 'bak')
AS
BEGIN

	DECLARE

		 @path NVARCHAR(256) -- path for backup file
		,@deldate DATETIME  -- calculated date for cutoff
		,@chardate NVARCHAR(50) -- calculated date for cutoff as string
		,@unit2	CHAR(2)	 -- cleaned up interval unit
		,@sqlstmt NVARCHAR(1000)
		,@params NVARCHAR(255)

	SET NOCOUNT ON 

My first step is to take that @unit parameter and transform it a bit. It may happen that someone, including myself, forgets how to specify the unit when running the procedure. They might specify ‘d’ or ‘dd’ or even ‘days’. So I decided to make the @unit a single character that would take all of these possibilities and boil them down into a single acceptable value, ‘D’. Then I could use internal logic to transform that into the value I’ll need later when I calculate my date. I also wanted to take any positive integers specified for the @period and make them negative, again for when I calculate my cutoff date later.

 			 @unit2 = CASE @unit
				WHEN 'H' THEN 'hh'
				WHEN 'D' THEN 'dd'
				WHEN 'W' THEN 'ww'
				WHEN 'M' THEN 'mm'
				WHEN 'Y' THEN 'yy'
				ELSE @unit
				END
			,@period = CASE
				WHEN @period > 0 THEN @period*-1
				ELSE @period
				END 

Calculating the date

Calculating the cutoff date is actually my next step. Unfortunately, the dateadd() function doesn’t accept a variable for the first parameter. The solution was to build a dynamic sql string and use sp_executesql to execute it and return my cutoff date. Because the xp_delete_file stored procedure accepts a string for the date parameter, rather than a datetime value, I also handle that conversion now.

 		SET @params = '@Units int, @dtOutput datetime OUTPUT'
		SET @sqlstmt = 'set @dtOutput = Dateadd(' + @unit2 + ',@Units, GetDate())'
		EXEC sp_executesql @sqlstmt, @params, @Units = @period, @dtOutput = @deldate OUTPUT

		SELECT @chardate = CONVERT(NVARCHAR(19),@deldate,126)	 

And now we’re ready to start deleting. I use a cursor to step through each of the directories I have listed in BackupPaths. Since I’ll be using the “include subdirectories” flag, there’s no need to cross join with my databases to get a list of each individual folder.

 		DECLARE paths CURSOR FOR
			SELECT BackupPath FROM maint.BackupPaths

		OPEN paths
		FETCH paths INTO @path
		WHILE @@FETCH_STATUS = 0
		BEGIN

			--SELECT
			EXECUTE master.dbo.xp_delete_file
				 0							-- 0 = backup file, 1 = report file
				,@path						-- base folder
				,@fileext					-- file extension
				,@chardate					-- older than this date
				,1							-- 1 = include first-level subfolders, 0 = don't include subfolders

			FETCH paths INTO @path

		END

		CLOSE paths
		DEALLOCATE paths 

Put it all together

And, with that, my backup file cleanup is done. Once again, I add some error handling and a sanity check (don’t want anyone trying to delete mdf files, do we?), and I have my completed procedure.

 USE master
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [maint].[sp_clean_backupdir]
	(@period smallint = 7, @unit char(1) = 'D', @fileext NVARCHAR(4) = 'bak')
AS
BEGIN

	DECLARE

		 @path NVARCHAR(256) -- path for backup file
		,@deldate DATETIME  -- calculated date for cutoff
		,@chardate NVARCHAR(50) -- calculated date for cutoff as string
		,@unit2	CHAR(2)	 -- cleaned up interval unit
		,@sqlstmt NVARCHAR(1000)
		,@params NVARCHAR(255)

	SET NOCOUNT ON

	BEGIN TRY

		SELECT
			 @unit2 = CASE @unit
				WHEN 'H' THEN 'hh'
				WHEN 'D' THEN 'dd'
				WHEN 'W' THEN 'ww'
				WHEN 'M' THEN 'mm'
				WHEN 'Y' THEN 'yy'
				ELSE @unit
				END
			,@period = CASE
				WHEN @period > 0 THEN @period*-1
				ELSE @period
				END

		IF @unit2 NOT IN ('hh', 'dd', 'ww', 'mm', 'yy')
			RAISERROR (N'Invalid interval unit specified.  Accepted values are H,D,W,M,Y.'
						,16
						,1)

		-- just in case we get stupid
		IF @fileext IN ('mdf', 'ndf', 'ldf')
			RAISERROR (N'Invalid file extension specified.  Cannot delete database files.'
						,16
						,1)

		SET @params = '@Units int, @dtOutput datetime OUTPUT'
		SET @sqlstmt = 'set @dtOutput = Dateadd(' + @unit2 + ',@Units, GetDate())'
		EXEC sp_executesql @sqlstmt, @params, @Units = @period, @dtOutput = @deldate OUTPUT

		SELECT @chardate = CONVERT(NVARCHAR(19),@deldate,126)

		DECLARE paths CURSOR FOR
			SELECT BackupPath FROM maint.BackupPaths

		OPEN paths
		FETCH paths INTO @path
		WHILE @@FETCH_STATUS = 0
		BEGIN

			--SELECT
			EXECUTE master.dbo.xp_delete_file
				 0							-- 0 = backup file, 1 = report file
				,@path						-- base folder
				,@fileext					-- file extension
				,@chardate					-- older than this date
				,1							-- 1 = include first-level subfolders, 0 = don't include subfolders

			FETCH paths INTO @path

		END

		CLOSE paths
		DEALLOCATE paths

	END TRY
	BEGIN CATCH

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

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

		-- Use RAISERROR inside the CATCH block to return
		-- error information about the original error that
		-- caused execution to jump to the CATCH block.
		RAISERROR (@ErrorMessage, -- Message text.
				   @ErrorSeverity, -- Severity.
				   @ErrorState -- State.
				   );
	END CATCH

END
GO

	 

So, to recap, we’ve gone through creating backup directories, performing the backup itself, and cleaning up old backups. And throughout the process we’ve developed a solution that’s low-maintenance, flexible, and, best of all, script-able. I’ll address other database maintenance tasks in the future, things like index maintenance, statistics, etc., so be sure to check back.

Also recommended:


Leave a comment

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

5 thoughts on “RYO Maintenance Plan – Database Backups, Part 3

  • dave

    Nice one Colleen :-)

    Before you call an end to your series on backups, can I suggest to include a word or two to explain the importance of checking the integrity of the backups you’ve created? If they don’t restore, they’re not backups :-)

  • Philip

    I would love to see a way to add logic that will verify at least X backup files for a given DB remain. Specifically, in my organization, I would like to delete all but the most recent one– even if that one is three weeks old because I took that particular DB offline or detached for one reason or another. As it is, I must now sort by file name, then delete all but the oldest one manually.