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.