Search This Blog

backup dbs with Tls

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 

SELECT REPLACE(REPLACE(REPLACE(CONVERT(char(16), CURRENT_TIMESTAMP, 120), '-', ''), ' ', ''), ':', '')

No comments: