4

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?

2

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"
5

My best recommendation for you is to use already applauded backup solution from Ola

CleanupTime

Specify the time, in hours, after which the backup files are deleted. If no time is specified, then no backup files are deleted.

I normally, use backup database ... with init and then delete the backup files older than X days.

Ola's solution is a one stop solution and its very flexible, so you can tune it as per your needs.

3

Unfortunately you will have to look elsewhere than in the BACKUP command to do what you are after in a automagical way.

Script it or use a 3rd party tool such as SQLSafe or RedGate SQL Backup.

If you read this short rant called Backup and the evil RETAINDAYS option (archive) you will see that is actually just sets some expire flag that will throw errors if you try to backup using INIT.

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.