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`""
Best Answer
Have you tried defining
$InstanceName
as a string literal with single quotations?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.