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
,@unit2CHAR(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
,@unit2CHAR(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.