(T-SQL) SQL Server parameterized scripts for backup a production database and restore into a test database

BackupDatabaseCopy_Parameterized.sql

DECLARE
    @TrailingPathDelimiter nchar(1) = N'\\',
    @BackupExtension nchar(4) = N'.bak',
    @Separator nchar(1) = N'_';
DECLARE
    @DbName nvarchar(max),
    @BackupPath nvarchar(max);
-- ** NO CHANGES BEFORE THIS LINE **


-- *******************************
-- ** Database to be backed up. **
-- *******************************
SET @DbName = N'ProductionDatabase';

-- *********************************************
-- ** Path to Backup Dump. Change, if needed. **
-- *********************************************
SET @BackupPath = N'C:\SqlDb\Backup';


-- ** NO CHANGES AFTER THIS LINE **
DECLARE
    @Iso8601Date nchar(17),
    @BackupFilename nvarchar(max),
    @SqlString nvarchar(max);

SELECT @Iso8601Date = FORMAT(GETUTCDATE(), N'yyyy-MM-ddTHHmmss')
IF RIGHT(@BackupPath, 1) != @TrailingPathDelimiter
BEGIN
    SET @BackupPath = @BackupPath + @TrailingPathDelimiter;
END;
SET @BackupFilename =
    @BackupPath + @Iso8601Date + @Separator + @@SERVERNAME + @Separator + @DbName + @BackupExtension;

-- Backup Database
SET @SqlString =
    N'BACKUP DATABASE [' + @DbName + N'] '
    + N'TO DISK = ''' + @BackupFilename + N''' '
    + N'WITH COPY_ONLY'
EXECUTE ( @SqlString );

RestoreDatabase_Parameterized.sql

DECLARE
    @DryRun bit,
    @DbName nvarchar(max),
    @BackupFilename nvarchar(max),
    @RowCount int;

DECLARE @Files AS TABLE
(
    Id int NOT NULL PRIMARY KEY IDENTITY(1, 1),
    LogicalName nvarchar(max),
    PhysicalName nvarchar(max)
);
-- ** NO CHANGES BEFORE THIS LINE **


-- *********************************************
-- ** Set DryRun to 0 for activating restore. **
-- *********************************************
SET @DryRun = 1;

-- ***********************************************************
-- ** Database to be restored. Warning! Will be overridden. **
-- ***********************************************************
SET @DbName = N'TestDatabase';

-- ******************************************************
-- ** Path and File to Backup Dump. Change, if needed. **
-- ******************************************************
SET @BackupFilename = N'C:\SqlDb\Backup\2017-01-13T090542_SERVER_ProductionDatabase.bak';

-- ********************************************************
-- ** Logical and Physical Filenames. Change, if needed. **
-- ********************************************************
INSERT INTO @Files (LogicalName, PhysicalName)
VALUES
    (N'ProductionDatabase', N'C:\SqlDb\TestDatabase.mdf'),
    (N'ProductionDatabase_log', N'C:\SqlDb\TestDatabase_log.ldf'); -- ** add more lines, if needed **


-- ** NO CHANGES AFTER THIS LINE **
SET @RowCount = @@ROWCOUNT;

DECLARE
    @NumRows int,
    @MoveString nvarchar(max),
    @LogicalName nvarchar(max),
    @PhysicalName nvarchar(max),
    @SqlString nvarchar(max);

SET @NumRows = @RowCount;
SET @MoveString = N'WITH ';

WHILE @NumRows > 0
BEGIN
    SELECT @LogicalName = LogicalName, @PhysicalName = PhysicalName
        FROM @Files
        WHERE Id = @RowCount - @NumRows + 1

    SET @MoveString = @MoveString + N'MOVE ''' + @LogicalName + N''' TO ''' + @PhysicalName + N'''';
    IF @NumRows > 1
    BEGIN
        SET @MoveString = @MoveString + N', ';
    END;

    SET @NumRows = @NumRows - 1;
END;

IF @DryRun = 1
BEGIN
    PRINT N'Dry run. Please check Logical and Physical Filenames. Set @DryRun to 0 for restore.';

    -- Restore Filelist
    RESTORE FILELISTONLY
    FROM DISK = @BackupFilename;

    -- Restore Verify
    SET @SqlString =
        N'RESTORE VERIFYONLY '
        + N'FROM DISK = ''' + @BackupFilename + N''' '
        + @MoveString
    EXECUTE ( @SqlString );
END
ELSE
BEGIN
    IF DB_ID(@DbName) IS NOT NULL
    BEGIN
        -- Set Database to single user mode
        SET @SqlString =
            N'ALTER DATABASE [' + @DbName + N'] '
            + N'SET SINGLE_USER WITH ROLLBACK IMMEDIATE'
        PRINT N'Set Database to single user mode.';
        EXECUTE ( @SqlString );
    END;

    -- Restore Database
    SET @SqlString =
        N'RESTORE DATABASE [' + @DbName + N'] '
        + N'FROM DISK = ''' + @BackupFilename + N''' '
        + @MoveString + N', '
        + N'REPLACE, '
        + N'RECOVERY, '
        + N'STATS=10'
    EXECUTE ( @SqlString );

    -- ** IMPORTANT **
    -- If there is no error in restore statement, the database will be in multiuser mode.
    -- If an error occurs, execute the following statement manually.
    --  It will convert the database back to multi user.
    SET @SqlString =
        N'ALTER DATABASE [' + @DbName + N'] '
        + N'SET MULTI_USER'
    PRINT N'Set Database to multi user mode.';
    EXECUTE ( @SqlString );
END;