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.

[sourcecode language=”sql”]
/*
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

[/sourcecode]

Posted

2012-11-05

Backup and Restore, T-SQL Script

Nick Xu

Tags:

Delete Backup, T-SQL

Comments

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

Robert JvR

2014-08-13

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

Reply
1. Nick Xu
  
  2014-08-13
  
  Thanks for adding this script using undocumented stored proc xp_delete_file whichi is much ‘safer’ than xp_cmdshell.
  
  Reply

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

Comments

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

Leave a Reply Cancel reply