USE master
GO

IF OBJECT_ID('BackupDbWithTs') IS NOT NULL DROP PROC BackupDbWithTs  
GO

CREATE PROC BackupDbWithTs
@db_name sysname
,@folder nvarchar(255)
,@backup_type varchar(13)
,@with_checksum char(1) = 'Y'
,@do_verification char(1) = 'Y'
,@copy_only char(1) = 'N'
AS

DECLARE 
 @sql nvarchar(4000)
,@filename nvarchar(255)
,@full_path_and_filename nvarchar(1000)
,@err_msg nvarchar(2000)
,@crlf varchar(2) = CHAR(13) + CHAR(10)
,@backup_extension varchar(10)

SET @backup_type = RTRIM(@backup_type)

--Verify valid backup type
IF @backup_type NOT IN('FULL', 'LOG', 'DIFF')
BEGIN
SET @err_msg = 'Backup type ' + @backup_type + ' is not valid.
Allowed values are FULL, LOG and DIFF'
RAISERROR(@err_msg, 16, 1)
RETURN -101
END

--Make sure folder name ends with '\'
IF RIGHT(@folder, 1) <> '\'
SET @folder = @folder + '\'

--Set file extension
IF @backup_type IN('FULL', 'DIFF')
 SET @backup_extension = '.bak'
ELSE
 SET @backup_extension = '.trn'

--Construct filename  
SET @filename = @db_name + '_backup_' + REPLACE(REPLACE(REPLACE(CONVERT(CHAR(19), CURRENT_TIMESTAMP, 120), '-', '_'), ' ', '__'), ':', '_')

--RAISERROR(@filename, 10, 1) WITH NOWAIT

--Construct full path and file name  
SET @full_path_and_filename = @folder + @filename + '_' + @backup_type + IIF(@copy_only = 'Y', '_COPY_ONLY', '') + @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 = 'DIFF'
SET @sql = @sql + ' DIFFERENTIAL,' + @crlf

IF @with_checksum <> 'N'
SET @sql = @sql + ' CHECKSUM,' + @crlf

IF @copy_only = 'Y'
SET @sql = @sql + ' COPY_ONLY,' + @crlf

--Add other backup options 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