2

I have a database backup running hourly between 7am and 7pm everyday, each file has a unique name. Is there a way that I can somehow add a 14 day retention time?

DECLARE @FileName AS VARCHAR(120)
DECLARE @Date AS CHAR(19)
SET @Date = CONVERT(CHAR(19), GETDATE(), 120)
SET @FileName = N'\\10.250.145.26\hfnfs\Trans\Hourly\South_RP' + @Date + '.bak'
BACKUP DATABASE [South_RP] TO DISK =@FileName

File name Example South_RP2012-06-26 11:00:00.bak

*Edit this is SQL sever 2008 R2

Chains
  • 12,541
  • 8
  • 45
  • 62
concept1483166
  • 39
  • 6
  • Please indicate somewhere that you're using MS SQL Server (there are lots of different kinds of SQL). – We Are All Monica Jun 26 '12 at 15:11
  • Do you want the backup command to automatically remove the backup after 14 days? It doesn't have this functionality. My hack for 7-day backups is to use the day-of-the-week in the name, rather than the date. This overwrites the backups after 7 days. – Gordon Linoff Jun 26 '12 at 15:15
  • I noticed this answer,but was unable to modify it to work. http://stackoverflow.com/a/1895886/1483166 – concept1483166 Jun 26 '12 at 15:18

2 Answers2

1

I would not do this using SQL Server. Instead, I would set up a scheduled task that runs a batch file similar to the following:

cd /d "%~dp0"
forfiles -d-14 -m*.bak -c"cmd /c del @PATH\@FILE"

Adapted from this answer. The way I wrote the batch file above, you'll want to place it in the same directory as the .bak files. Please, test it with echo first instead of del.

To make it work, you'll need to get the forfiles.exe utility from Microsoft's FTP site and place it in your C:\WINDOWS\system32 directory.

If you really want to start this command from SQL Server, you can use the xp_cmdshell procedure to run the batch file that deletes old items.

Community
  • 1
  • 1
We Are All Monica
  • 13,000
  • 8
  • 46
  • 72
0

You'll need to set up a cron job/scheduled task to simply delete any backups that are >14 days old.

Sean Johnson
  • 5,567
  • 2
  • 17
  • 22