This script is a parameterized stored procedure, [dbo].[DB_BACKUP_TO_DISK], to backup Sql Server Database (.BAK). Everything is to call this procedure with all parameters. See below table to understand parameters.
Parameter Name | Type | Description |
@DBName | VARCHAR(500) | Put Database name |
@SaveAs | VARCHAR(500) | Put output name, is a bak file name. i.e. TEST_BACKUP.BAK |
@SaveTo | VARCHAR(500) | Put full path where BAK file will be created |
See below how to call this procedure…
EXEC [dbo].[DB_BACKUP_TO_DISK] @DBName = N'TEST_DATABASE', @SaveAs = N'TEST_BACKUP', @SaveTo = N'D:\TEST\' |
Please find the below script of the described stored procedure …
CREATE PROCEDURE [dbo].[DB_BACKUP_TO_DISK] @DBName VARCHAR(500) , @SaveAs VARCHAR(500) , @SaveTo VARCHAR(500) AS BEGIN SET @DBName = LTRIM(RTRIM(@DBName)) SET @SaveAs = LTRIM(RTRIM(@SaveAs)) SET @SaveTo = LTRIM(RTRIM(@SaveTo)) DECLARE @fileName VARCHAR(4000) = @SaveTo + CASE WHEN right(@SaveTo, 1) '\\' THEN '\\' ELSE '' END + @SaveAs + + CASE WHEN right(@SaveAs, 4) '.BAK' THEN '.BAK' ELSE '' END BACKUP DATABASE @DBName TO DISK = @fileName END |
see also -- Use Side-Effecting Operator /DML within a User Defined Function of SQL Server
-- SQL Script to split string using SQL Server