Sqlcmd command strips $ out of query variable

Question

What I am trying to do

Restore a database from backup using sqlcmd. Sqlcmd is being called by PowerShell.

What is the issue

The server folder which contains the database backups has a $ inside of the name. ie Hostname$InstanceName. When I try to restore any databases I get an error and notice it is trying to restore from \\networkshare\Hostname\DatabaseName\Database.bak

You'll notice that everything after the $ got stripped out. I believe this is due to sqlcmd's support of variables (-v parameter). I have tried to use the -x parameter (disable variable substitution) with no success.

This process works fine with folders that do not have $ inside of their names.

Does anyone know what I can try from here? Or see if I'm doing something wrong.

Thanks

Sample code

$Source_Server = "Hostname`$InstanceName"
$FQFile_DatabaseBackup = $Network_Share + "\" + $Source_Server + "\" + $Source_Database + "\Database.bak"
$Query_RestoreDatabase = "RESTORE DATABASE [$Source_Database] FROM DISK = '$FQFile_DatabaseBackup' WITH RECOVERY, REPLACE"
Invoke-Expression -Command "sqlcmd -x -S `"$Destination_Server`" -d `"master`" -E -Q `"$Query_RestoreDatabase`""

• user507: You might try making $source_server = "Hostname+CHAR(36)+InstanceName"

Answer 1

Have you tried defining $InstanceName as a string literal with single quotations?

PS C:\> $GoodInstanceName = '$myInstanceName'
PS C:\> "Hostname$GoodInstanceName"
Hostname$myInstanceName
PS C:\> $BadInstanceName = "$myInstanceName"
PS C:\> "Hostname$BadInstanceName"
Hostname

In Powershell, string literals that are defined with double-quotes will resolve variables. However, if they are defined with single-quotes, they will not. Since $myInstanceName resolved in the $BadInstanceName example but has never been defined, it will simply resolve to an empty string.

I suspect this (or something similar) is the problem since the error seems to do with your completed path string.

• jsauni: I tried using a string literal with no luck. The $InstanceName is still being stripped out. I Write-Host $Query_RestoreDatabase before running the Invoke-Expression and can confirm the Restore statement contains the Hostname$InstanceName as it should. I still believe it is sqlcmd stripping the $InstanceName out.
• Anthony Neace: Have you tried Invoke-sqlcmd or the call operator & " " instead? Even if you don't want to use either of those solutions permanently, it would be a good first step towards troubleshooting if either of them work and Invoke-Expression doesn't.
• jsauni: I tested Invoke-Sqlcmd and this works as expected. I had originally developed this process using Invoke-Sqlcmd. The reason why I started using sqlcmd is that Invoke-Sqlcmd seemed to issue the restore statement and then carry on with the powershell script. Eventually the script drops the restored database, and I was running into issues/errors where I was trying to drop a database I was restoring. I think I read somewhere that Invoke-Sqlcmd was not transaction aware?
• Anthony Neace: I see. I can't speak to the more specific behaviors of Invoke-Sqlcmd any better than the docs would, but I can offer you some additional options to call sqlcmd to behave in the same way it would from cmd. I've personally had success before using cmd /c, which bypasses powershell entirely and runs it as if cmd were running it.
• jsauni: Thanks for your help, wrapping single quotes instead of double quotes around the Invoke-Expression -Command parameter value seems to have resolved the issue.