T-SQL Script to Delete Older-Than-N-Days Backups in a Specific Folder

I’ve noticed there are quite some hits on this post and the old format of the T-SQL code looked a bit messy, so I’ve done a makeover of the script on 2014-04-10.

This script requires a not-too-strict security setting for sql server’s service account, because it will have to execute Windows batch commands to achieve the goals of removing older backups.

/*
Script to delete older than N days backup from a specific directory
Author: Nick Xu
Date: 2014-04-10
Applied version: 2000, 2005+
*/
 
DECLARE @N AS VARCHAR(2) -- days for retention
DECLARE @path AS VARCHAR(128) -- the path for deletion
DECLARE @cmd AS VARCHAR(512) -- the actually command
 
SET @N = '3' -- change the days here, remember it is type VARCHAR
SET @path = 'C:\backups' -- ending back slash is not necessary
SET @cmd = 'forfiles /P "' + @path + '" /S /M *.bak /D -' + @N + ' /C "cmd /c del @PATH"'
 
EXEC master.dbo.xp_cmdshell @cmd
 
/*
The following part is for version 2005+.
 
You will need the following to enable xp_cmdshell in SQL Server to let service account EXECute windows cmd's.
 
But you might have to work with your angry security officer to get this done.
*/

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
 
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO

Advertisements

About Nick Xu

Not a DBA anymore
This entry was posted in Backup and Restore, T-SQL Script and tagged , . Bookmark the permalink.

2 Responses to T-SQL Script to Delete Older-Than-N-Days Backups in a Specific Folder

  1. Robert JvR says:

    DECLARE @name VARCHAR(50); — Database name
    DECLARE @path VARCHAR(256); — Path for backup files
    DECLARE @fileName VARCHAR(256); — Filename for backup
    DECLARE @fileDate VARCHAR(20); — Used for file name
    DECLARE @DeleteDate DATETIME = DATEADD(DAY,-1,GETDATE());
    — Delete backups older than the 1 day before now

    — Path to backups.
    SET @path = ‘B:\SQL Backups\DatabaseName\’;

    — Get date to include in file name.
    SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112);

    — Dynamically get each database on the server.
    DECLARE db_cursor CURSOR FOR
    SELECT name
    FROM master.sys.databases
    WHERE name IN (‘DatabaseName’)

    OPEN db_cursor;
    FETCH NEXT FROM db_cursor INTO @name;

    — Loop through the list to backup each database.
    WHILE @@FETCH_STATUS = 0
    BEGIN
    — Build the path and file name.
    SET @fileName = @path + @name + ‘_’ + @fileDate + ‘.BAK’;
    — Backup the database.
    BACKUP DATABASE @name TO DISK = @fileName WITH INIT;
    — Loop to the next database.
    FETCH NEXT FROM db_cursor INTO @name;
    END

    — Purge old backup files from disk.
    EXEC master.sys.xp_delete_file 0,@path,’BAK’,@DeleteDate,0;

    — Clean up.
    CLOSE db_cursor;
    DEALLOCATE db_cursor;
    GO

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