7

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

migrated from stackoverflow.com May 11 '14 at 9:03

This question came from our site for professional and enthusiast programmers.

  • 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.

  • 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 ;
-1

It's not possible to create a backup that will autodestroy after a number of hour, but it's possible to create one that will expire, e.g. a backup that will be overwritten after a specific date when the next backup will be performed.
To do this EXPIREDATE is the parameter to be set like

BACKUP DATABASE AdventureWorks2012
TO DISK = 'Z:\SQLServerBackups\AdventureWorks2012.Bak'
WITH EXPIREDATE = DATEADD(hour, 3, GETDATE()) ;

The backup will not be deleted, but the next backup after the expire date will use the disk space of the expired ones.

  • No, the backup will not ovewrite the existing expired backups. It will append the same way, but prevent from restore the expired ones. – Ricardo Souza Mar 4 '15 at 14:16
  • @rcdmk the BOL page on BACKUP says that EXPIREDATE specifies when the backup set for this backup can be overwritten. I've never used it myself, but I think that the BOL is a trusty source. – Serpiton Mar 4 '15 at 14:25
  • And this information is right. It confuses everyone, including me, but now I know it won't overwrite the old backups by itself. This option only allows you to overwrite backups that had expired and error out when you try to overwrite a backup that did not expired yet. – Ricardo Souza Mar 4 '15 at 15:39

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.