$ sign in name not working in my script

PowerShell Help
1

I have a sql database that I use sqlcmd to backup with ReportServer$SCCMTempDB

The command runs in a dos window no problem from my powershell script it truncates the name after the $sign

sqlcmd -S TGCS035\SCCM -d master -Q “EXECUTE dbo.DatabaseBackup @Databases = ‘ReportServer$SCCMTempDB’, @Directory = 'H:\Backup', @BackupType = ‘FULL’, @Verify = ‘Y’, @CleanupTime = 120” -b >>$LogFolder$LogFile

 

PS C:\Util> sqlcmd -S TGCS035\SCCM -d master -Q “EXECUTE dbo.DatabaseBackup @Databases = ‘ReportServer$SCCMTempDB’, @Directory = 'H:\Backup', @BackupType = ‘FULL’, @Verify = ‘Y’, @CleanupTime = 120” -b
Date and time: 2020-09-06 21:37:00
Server: TGCS035\SCCM
Version: 12.0.6372.1
Edition: Enterprise Edition (64-bit)
Procedure: [master].[dbo].[DatabaseBackup]
Parameters: @Databases = ‘ReportServer’, @Directory = 'H:\Backup', @BackupType = ‘FULL’, @Verify = ‘Y’, @CleanupTime = 120, @CleanupMode = ‘AFTER_BACKUP’, @Compress = NULL, @CopyOnly = ‘N’, @ChangeBackupType = ‘N’, @BackupSoftwa
re = NULL, @CheckSum = ‘N’, @BlockSize = NULL, @BufferCount = NULL, @MaxTransferSize = NULL, @NumberOfFiles = NULL, @CompressionLevel = NULL, @Description = NULL, @Threads = NULL, @Throttle = NULL, @Encrypt = ‘N’, @EncryptionAlgo
rithm = NULL, @ServerCertificate = NULL, @ServerAsymmetricKey = NULL, @EncryptionKey = NULL, @ReadWriteFileGroups = ‘N’, @OverrideBackupPreference = ‘N’, @NoRecovery = ‘N’, @URL = NULL, @Credential = NULL, @MirrorDirectory = NULL
, @MirrorCleanupTime = NULL, @MirrorCleanupMode = ‘AFTER_BACKUP’, @LogToTable = ‘N’, @Execute = ‘Y’
Source: https://ola.hallengren.com

Date and time: 2020-09-06 21:37:00

I have tried double quotes a back slash in front of the $ sign

 

Any ideas

 

 

2

this is because $ is used to represent variable in PowerShell and in this case it tries to interpret $SCCMTempDBvariable which doesn’t exists and finally translates to null. Wrapping up the string in single quote will make PowerShell to treat it as literal string, but in this case the whole string is wrapped in double quotes, hence anything inside with $ will be treated as variable. You escape it using backtick which is the escape character in PowerShell.

sqlcmd -S TGCS035\SCCM -d master -Q “EXECUTE dbo.DatabaseBackup @Databases = ‘ReportServer<use backtick here>$SCCMTempDB’, @Directory = ‘H:\Backup\’, @BackupType = ‘FULL’, @Verify = ‘Y’, @CleanupTime = 120”