2

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`""
Improve this question
1
  • You might try making $source_server = "Hostname+CHAR(36)+InstanceName"
    – user507
    Sep 11, 2013 at 4:39

1 Answer 1

Reset to default
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.

Improve this answer
6
  • 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.
    – jsauni
    Sep 19, 2013 at 0:09
  • 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.
    – Anthony Neace
    Sep 19, 2013 at 0:21
  • 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?
    – jsauni
    Sep 19, 2013 at 0:54
  • 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.
    – Anthony Neace
    Sep 19, 2013 at 1:24
  • 1
    Thanks for your help, wrapping single quotes instead of double quotes around the Invoke-Expression -Command parameter value seems to have resolved the issue.
    – jsauni
    Sep 19, 2013 at 2:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.