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.
Document history
7/4/2010 | Initial release. |