Invoke-Sqlcmd define new variable error

702 views Asked by At

I have the below function:

Function Backup-SQLDatabase { #MP-RestoreDatabase
    Param(
        [Parameter(Mandatory)]
        [String]$Server,
        [Parameter(Mandatory)]
        [String]$Database,
        [Parameter(Mandatory)]
        [String]$BackupFile,
        [Parameter(DontShow)]
        [String]$SQLScriptPath = "$($PSScriptRoot)\SQLFiles\"
    )  

    Import-Module SQLPS

    Invoke-Sqlcmd -ErrorLevel 1 -InputFile "$($SQLScriptPath)BackupDatabase.sql" `
        -Variable DBName=$Name, BackupLocation=$BackupFile `
        -ServerInstance $server `
        -ErrorAction Stop
}

Which is calling this SQL script:

use master
go

Declare @DBName varchar(max)
Declare  @BackupLocation varchar(max)
Declare @sql nvarchar(max)

set @DBName = '$(DBName)'
set @BackupLocation = '$(BackupLocation)'

SET @sql = N'BACKUP DATABASE ['+ @DBName + '] TO  DISK = N''' + @BackupLocation + ''' WITH  COPY_ONLY, NOFORMAT, NOINIT,  NAME = N''' + @DBName + '-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD,  STATS = 10;'

EXEC sp_executesql @sql

But when I call it, I receive the error

The format used to define the new variable for Invoke-Sqlcmd cmdlet is invalid.

The values I'm passing in are:

  • Database Name: uat.test.co.uk
  • Server Instance: MSGUXXXXX01
  • Backup Path: \\mstXXXXX01.XXXXX.local\SQL_Backups_Temp\ScriptBackups\20170907083425-uat.test.co.uk.bak

I feel like there's something very obvious that I'm missing here, but after looking at it for an hour, I'm stumped.

0

There are 0 answers