7

Can anyone please help me with a TSQL commmand to delete backup files older than N hours?

1
  • 8
    Please consider to show your effort. – timo.rieber May 11 '14 at 8:12
6

Remember that T-SQL should not be used to do file maintenance. Instead you should use Powershell to delete /move / copy etc files.

Tsql way:

use xp_cmdshell command along with the FORFILES command

e.g Delete all .sql files in the C:\Backup directory and its subfolders where the file modified date is more than 30 days old.

EXEC xp_cmdshell 'FORFILES /p c:\BACKUP /s /m *.sql /d -30 /c "CMD /C del /Q /F @FILE"'

Disclaimer: Enabling xp_cmdshell is a security risk and not many companies allow it.

Powershell Way --> This should be the right approach !!

A full script can be found at technet.

## Delete files older than 90 days

$a = Get-ChildItem C:\Scripts
foreach($x in $a)
    {
        $y = ((Get-Date) - $x.CreationTime).Days
        if ($y -gt 90 -and $x.PsISContainer -ne $True)
            {$x.Delete()}
    }
5

If you want to remove files pragmatically you could use the master.dbo.xp_delete_file extended stored procedure but it is not generally recommended to use this as these types of procedure are scheduled to be removed in a future version of SQL Server although I'm not sure when that will be.

If this is something that could possibly be in place for a long time after deployment I would recommend you use a maintenance plan as this functionality is built in.

1
  • If you want to do this through T-SQL, the xp_delete_file is your best option. As @JamesAnderson says, it's undocumented, but I have not seen any issues with it since the early releases of SQL Server 2005. Review my article if you would like to see the details. patrickkeisler.com/2012/11/… – Patrick Keisler May 13 '14 at 10:02
1

Below is the T-SQL to delete old backups from a location having age as N:

EXECUTE master.dbo.xp_delete_file    
    0,
    N'**FILEPATH OF OLD BACKUPS LOCATION**',
    N'bak',
    N'**DATE BEFORE WHICH YOU DONT WANT TO KEEP BACKUPS** T **TIME**',
    1 ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.