Nucleus Datakonsult TSQL script to create backup file including database name, date and time


About me and Nucleus

Articles, tips etc.
My blog
Microsoft articles

Courses and training

Microsoft Most Valuable Professional


There's no support at the TSQL 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. You can call this procedure from a TSQL task in a MP and also use the built-in support in MP to remove old backup files.
Article written: 2007-09-25
Article last modified: 2008-01-12

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.

This script was written for SQL Server 2005 or later. It should run fine on 2000 with some minor modifications. One modification that comes to mind is removing the CHECKSUM option.

There's little protection from SQL injection in the script. Don't allow end-users directly call the procedure.
For a more comprehensive TSQL based solution which also includes other maintenance tasks (reindex, integrity checks etc), see Ola Hallengren's scripts.

Usage examples
EXEC BackupDbWithTs
@db_name 'pubs'
,@folder 'C:\x'
,@backup_type 'DATABASE'
,@backup_extension 'bak'
,@with_checksum 'Y'
,@do_verification 'Y'

EXEC BackupDbWithTs 'pubs''C:\Temp\pubs''DATABASE''bak''Y''N'
EXEC BackupDbWithTs 'pubs''C:\Temp\pubs''LOG''trn'
EXEC BackupDbWithTs 'pubs''C:\Temp\pubs''DIFFERENTIAL''bak'

The code:
You find the code here.