SQL Server – How to Correctly Discard Old Backups

backupsql serversql server 2014ssis

I have an Execute SQL Task in my SSIS control flow that's backing up a number of databases before it proceeds to run the overnight ETL:

EXECUTE master.dbo.xp_create_subdir N'G:\Backups\DQ'
GO
EXECUTE master.dbo.xp_create_subdir N'G:\Backups\master'
GO
EXECUTE master.dbo.xp_create_subdir N'G:\Backups\Metadata'
GO
EXECUTE master.dbo.xp_create_subdir N'G:\Backups\NDS'
GO
EXECUTE master.dbo.xp_create_subdir N'G:\Backups\Staging'
GO
BACKUP DATABASE [DQ] TO  DISK = N'G:\Backups\DQ\DQ.bak' WITH  RETAINDAYS = 3, NOFORMAT, NOINIT,  NAME = N'DQ', SKIP, REWIND, NOUNLOAD,  STATS = 10
GO
BACKUP DATABASE [master] TO  DISK = N'G:\Backups\master\master.bak' WITH  RETAINDAYS = 3, NOFORMAT, NOINIT,  NAME = N'master', SKIP, REWIND, NOUNLOAD,  STATS = 10
GO
BACKUP DATABASE [Metadata] TO  DISK = N'G:\Backups\Metadata\Metadata.bak' WITH  RETAINDAYS = 3, NOFORMAT, NOINIT,  NAME = N'Metadata', SKIP, REWIND, NOUNLOAD,  STATS = 10
GO
BACKUP DATABASE [NDS] TO  DISK = N'G:\Backups\NDS\NDS.bak' WITH  RETAINDAYS = 3, NOFORMAT, NOINIT,  NAME = N'NDS', SKIP, REWIND, NOUNLOAD,  STATS = 10
GO
BACKUP DATABASE [Staging] TO  DISK = N'G:\Backups\Staging\Staging.bak' WITH  RETAINDAYS = 3, NOFORMAT, NOINIT,  NAME = N'Staging', SKIP, REWIND, NOUNLOAD,  STATS = 10

The intent is to only retain 3 days, and discard anything older.

The problem is that the .bak files just keep growing and growing for a week or two, until the G drive fills up and the Execute SQL Task fails, which aborts the overnight ETL.

It seems the .bak is apparently not discarding old backups; looks like I could go back all the way to the last time I deleted all the backups to free some disk space on that G drive:

"restore to" dialog showing full db backups going back over a whole week

The question is simple: it looks like my Execute SQL Task is doing it wrong. How do I fix it? Or is it something else?

Best Answer

You will want to switch to using a backup statement of:

BACKUP DATABASE [Dbname] TO DISK = <Path> + <BackupName> WITH INIT

Build your BackupName using a variable that includes maybe the package runtime. Convert that timestamp to just something like yyyymmddm where you full backup file name comes out to something like: DatabaseName_FULL_2015122205.bak

You could then just add a process task in your package to execute a PowerShell command to delete files older than 3 days. Your process could be:

powershell.exe -Command "Get-ChildItem $BackupDirectory -Include *.bak -Recurse | where {$_.LastWriteTime -le ( (Get-Date).AddDays(-3) )| Remove-Item -force"
Related Question