SQL Back up RetainDays Option and deleting older back up files

When Sage 500 creates a back up plan, the Step within the job has the Option RetainDays included with it.  The question came up today if this option deletes the older back files the job created.  The option does not delete any back up files.  The option RETAINDAYS achieves a very different purpose with Backup. It just prevents users from overwriting the backup file if the user is trying to do it with INIT option. Otherwise, it really does not do anything else.  If the user is using the FORMAT option, the backup will be overwritten anyway. This means the use of RETAINDAYS is very much limited.

If you need a way to delete older backup files, you can create a .BAT file or a PowerShell script which can then be scheduled with the Task Scheduler to run automatically.

The code for the .BAT file would be similar to the following:

c:\Windows\System32>ForFiles /p "C:\Junk" /s /d -30 /c "cmd /c del @file"

Substitute the folder path and the amount of days with desired values and you are done

For PowerShell it would be as follows:

# Delete all Files in a folder older than 30 days

$Path = "C:\Junk"
$Daysback = "-30"

$CurrentDate = Get-CurrentDate
$DatetoDelete = $CurrentDate.AddDays($Daysback)
Get-ChildItem $Path | Where-Object { $_.LastWriteTime -lt $DatetoDelete } | Remove-Item

  • Great discussion topic Lou!

    There is a separate task that would accomplish this in SQL Management Studio. We love to use and recommend the use of the scripts from Ola Hallengren for backups but truly like them for the Index Optimization tasks.

    https://ola.hallengren.com/sql-server-backup.html

    This can be configured to perform your DBCC CHECKDB but also use it to configure what indexes should be rebuilt versus reorg. This optimizes how the indexes are rebuilt and minimizes the indexes that get rebuilt completely based on fragmentation. So the reindex can be run in a shorter time period which is great for companies running 24x7 as you still get the benefit of the rebuild while minimizing any disruption the rebuild may have. 

    Another benefit of the reduction on rebuild of all indexes is the reduction of the disk io that would be occurring on the system. This information effects the SQL Dynamic Management Views (DMVs) that you can use to look at system performance. So now they are more about the transactional use of the SQL versus the maintenance going on in SQL. 

  • Lou - I do it like you do.  We have an added twist though.  I bought an 8 terabyte drive and copy everything there as a double back flip emergency if there is an emergency backup.  We delete our backups after 7 days using powershell on the main backup volume but keep the 8 terabyte drive with months of backups.  We also use an onsite backup product called Rapid Recovery and that is mirrored offsite.

    Joe - I don't use Ola's stuff because we are not 24x7 but his scripts are considered the gold standard.