How to delete one week old files using T-SQL

Question

I run T-SQL script below using SQL Server Agent to backup all my databases in MSSQL server. I would like to improve script by adding option to delete one week old databases backups from file system. Could anyone point me how to do that using T-SQL?

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 

SET @path = 'C:\www\databases\Backup\'  

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 

DECLARE db_cursor CURSOR FOR  
SELECT name 
FROM master.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb')  

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   

WHILE @@FETCH_STATUS = 0   
BEGIN   
       SET @fileName = @path + @name + '_' + @fileDate + '.BAK'  
       BACKUP DATABASE @name TO DISK = @fileName  

       FETCH NEXT FROM db_cursor INTO @name   
END   

CLOSE db_cursor   
DEALLOCATE db_cursor

What version of SQL Server? If it's 2005 or higher, why not just use a maintenance plan; there are pre-defined tasks for deleting old files and removing maintenance history. Doing file operations from TSQL is awkward at best. — Pondlife, Apr 20 '11 at 10:53

score 1 · Answer 1 · edited May 23 '17 at 12:29

1

You could also directly use xp_delete_file (undocumented), which is what maintenance plans use.

This post had more details on its use.

edited May 23 '17 at 12:29

Community

1
1

answered Apr 20 '11 at 21:10

GilM

3,711
17
18

This deletes files before a specific date. So if you have any db backups that you want to save then your SOL. – QueueHammer Aug 29 '13 at 15:41

How to delete one week old files using T-SQL

1 Answers1