Thursday, February 4, 2016

SQL Script/tSql to backup SQL Server Database

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


No comments:

Post a Comment