Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Cleveland DBA

Colleen Morrow is a Database Engineer for a software company in Westlake, Ohio. She has worked in the IT industry for 15+ years, doing everything from technical support to development to database administration on Informix, Oracle, and SQL Server platforms.

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.

Comments

Leave a comment on the original post [colleenmorrow.com, opens in a new window]

Loading comments...