Overview
There's no support at the T-SQL level to keep generations of backups. For instance, you want to keep backups for 3 days. The functionality is available in Maintenance Plans (MP), but sometimes you want a higher degree of customization. Implementing this functionality requires some pretty basic TSQL coding.
Here you find a stored procedure that you can use which mimics how MP names backup files.
Article written: 2007-09-25
Article last modified: 2016-03-15
BackupDbWithTs.sql
Acknowledgements
Thanks to PeterW for reminding me of using QUOTENAME so we don't break with non-standatd identifier for database name and also some protection against injection.
Versions
This script is written for SQL Server 2008 or later.
Comments
There's little protection from SQL injection in the script. Don't allow end-users directly call the procedure.
For a more comprehensive T-SQL based solution which also includes other maintenance tasks (reindex, integrity checks etc), see Ola Hallengren's scripts.
Usage examplesEXEC BackupDbWithTs
@db_name = 'Adventureworks2014'
,@folder = 'C:\Temp'
,@backup_type = 'FULL'
,@with_checksum = 'Y'
,@do_verification = 'Y'
,@copy_only = 'N'
EXEC BackupDbWithTs 'Adventureworks2014', 'C:\Temp', 'DATABASE', 'Y', 'N', 'Y'
EXEC BackupDbWithTs 'Adventureworks2014', 'C:\Temp, 'LOG'
EXEC BackupDbWithTs 'Adventureworks2014', 'C:\Temp, 'DIFFERENTIAL'