Home
About me and Nucleus
Partners
Links
Articles, tips etc.
My blog
Microsoft articles
Newsgroups
Course demo files
Sync logins
SMTP Alerter
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
Acknowledgements
Thanks to PeterW for remining 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 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.
Comments
There's no 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:\Temp\pubs'
,@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:
IF OBJECT_ID('BackupDbWithTs') IS NOT NULL DROP PROC BackupDbWithTs
GO
CREATE PROC BackupDbWithTs
@db_name sysname
,@folder nvarchar(255)
,@backup_type varchar(13)
,@backup_extension varchar(10)
,@with_checksum char(1) = 'Y'
,@do_verification char(1) = 'Y'
AS
DECLARE @sql nvarchar(4000)
DECLARE @filename nvarchar(255)
DECLARE @full_path_and_filename nvarchar(1000)
DECLARE @err_msg nvarchar(2000)
DECLARE @crlf varchar(2)
SET @crlf = CHAR(13) + CHAR(10)
--Verify valid backup type
IF @backup_type NOT IN('DATABASE', 'LOG', 'DIFFERENTIAL')
BEGIN
SET @err_msg = 'Backup type ' + @backup_type + 'is not valid. Allowed values are DATABASE, LOG and DIFFERENTIAL'
RAISERROR(@err_msg, 16, 1)
RETURN -101
END
--Make sure folder name ends with '\'
IF RIGHT(@folder, 1) <> '\'
SET @folder = @folder + '\'
--Make file extension starts with '.'
IF LEFT(@backup_extension, 1) <> '.'
SET @backup_extension = '.' + @backup_extension
--Construct filename
SET @filename = @db_name + '_backup_' + REPLACE(REPLACE(REPLACE(CONVERT(char(16), CURRENT_TIMESTAMP, 120), '-', ''), ' ', ''), ':', '')
--Construct full path and file name
SET @full_path_and_filename = @folder + @filename + @backup_extension
--Construct backup command
SET @sql = 'BACKUP ' + CASE @backup_type WHEN 'LOG' THEN 'LOG' ELSE 'DATABASE' END + ' ' + QUOTENAME(@db_name) + @crlf
SET @sql = @sql + 'TO DISK = ' + QUOTENAME(@full_path_and_filename,'''') + @crlf
SET @sql = @sql + 'WITH' + @crlf
SET @sql = @sql + ' NOINIT,' + @crlf
SET @sql = @sql + ' NAME = ' + QUOTENAME(@filename,'''') + ',' + @crlf
IF @backup_type = 'DIFFERENTIAL'
SET @sql = @sql + ' DIFFERENTIAL,' + @crlf
IF @with_checksum <> 'N'
SET @sql = @sql + ' CHECKSUM,' + @crlf
--Add backup option below if you want to!!!
--Remove trailing comma and CRLF
SET @sql = LEFT(@sql, LEN(@sql) - 3)
--PRINT @sql
EXEC(@sql)
IF @do_verification = 'Y'
RESTORE VERIFYONLY FROM DISK = @full_path_and_filename
GO