Backup File Clean-Up

Here is a handy little stored procedure to delete backup files from your file system. It makes use of the undocumented system stored procedure xp_delete_file; I’ve provided some information on the parameters the procedure accepts in the example below.

FYI, xp_delete_file will only delete SQL backup files or SQL maintenance plan reports, so don’t try to use it to delete non-SQL files on your file system. And it doesn’t simply look at the file extension either… it actually parses the file header information to determine the file type.

While this sample clean-up procedure is rather simple, it could easily be extended to accept additional parameters to calculate the date boundary, whether to delete the backup history as well as (or instead of) backup files, whether to traverse sub-folders, and so on.

For this example we’ll create the procedure in our maintenance schema (mx). Once created you can simply run it on-demand, through the SQL Agent, or via the windows task scheduler with the command-line SQL utility.

Enjoy!

CREATE PROCEDURE mx.uspCleanUp
    @strDirectory NVARCHAR(50)
    , @strFileExtention CHAR(3) = 'bak' -- options: bak, trn
    , @dtDeleteBefore DATE = NULL
AS
BEGIN

    -- Declare and initialize variables
    DECLARE @dt DATE =
        ISNULL(@dtDeleteBefore, DATEADD(WEEK, -2, GETDATE()));

    -- Delete the backup history older than the specified date
    EXECUTE msdb.dbo.sp_delete_backuphistory
        @oldest_date = @dt
    ;

    -- Delete the backup files older than the specified date
    EXECUTE master.dbo.xp_delete_file
        0 -- file type; 0 = backup, 1 = report
        , @strDirectory -- directory in which to look for files
        , @strFileExtension -- extension of files to look for (no period!)
        , @dt -- delete files older than this date
        , 1 -- traverse sub-folders; 0 = FALSE, 1 = TRUE
    ;

END

Advertisements
Post a comment or leave a trackback: Trackback URL.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: