Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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`""
share|improve this question
    
You might try making $source_server = "Hostname+CHAR(36)+InstanceName" –  Shawn Melton Sep 11 '13 at 4:39

1 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.

share|improve this answer
    
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 '13 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. –  Hyper Anthony Sep 19 '13 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 '13 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. –  Hyper Anthony Sep 19 '13 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 '13 at 2:51

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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