It's a common business need that you want to retain only a
certain number of backups at any given time. The standard
T-SQL BACKUP syntax does not
have any file retention options. SQL Server maintenance plans
do offer you the option to delete old backup files, but that is a
maintenance plan option and not part of the BACKUP syntax.
With SQL Backup, you can specify the number of backups to retain
in the BACKUP command itself.
Backup files older than this retention period are deleted,
and only when the current backup has completed successfully.
For e.g. the following command will delete all full database
backup files for the AdventureWorks in the 'g:\backups\' directory
that are older than 2 days.
You can also provide the retention period in hours, by appending
a 'h' qualifier. For e.g. the following command will delete
all backup files older than 2 hours.
If you would rather specify a retention period in terms of
backup sets, that is also possible in SQL Backup. Just append
the 'b' qualifier to the retention period. For e.g. the
following command will retain only 2 full database backup sets of
the AdventureWorks database in the 'g:\backups\' directory, and
delete any extras.
This is certainly much easier than writing batch file commands,
Powershell scripts and the like.
Discuss or comment on this article on our
Facebook group.