0

So I'm working on a server application for a large government body and need to setup a development environment to test on. For this I'm using a Vagrant-controlled VM with Windows Server 2019 installed as the guest OS and SQL Server 2019 as the DB engine.

After creating and subsequently destroying the VM, then rebuilding it I am now trying to restore a DB backup to a fresh instance with the exact same instance name, logins etc. But I get this error from my PowerShell script:

PS C:\Users\vagrant> Invoke-SqlCmd -Query "RESTORE DATABASE DBName FROM DISK = '.\db-name.bak'"

Invoke-SqlCmd : Cannot open backup device 'C:\Program Files\Microsoft SQL Server\MSSQL15.JIRADB\MSSQL\Backup\db-name.bak'. Operating system     
error 2(The system cannot find the file specified.).
RESTORE DATABASE is terminating abnormally.
At line:1 char:1
+ Invoke-SqlCmd -Query "RESTORE DATABASE DBName FROM DISK = '.\db-name.bak'"
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException
    + FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

What gives?! Clearly the backup file now exists at C:\Users\vagrant but SQL Server seems to think that it should be in the original, default backup location.

Obviously, I can simply move the backup file back to the original location, but I'd like to know why SQL Server is expecting that. MTIA! :D

1

The .\ is not interpreted in the Query parameter as 'current directory' and so SQL looks for the file in its default backup location. You have a couple of options

  1. Change the Query value and specify the full path to the file
  2. Pass a variable into the Query string (see below)

Option 1 is the simplest, but you may have need of using variables elsewhere, so here's an example:

$StrArray = "VAR1='C:\Temp\db-name.bak'"
invoke-sqlcmd -query "RESTORE HEADERONLY FROM DISK = `$(VAR1)" -Variable $StrArray

$StrArray is an array of strings that take the form Variable Name='Variable Value' and each is substituted in your query string at run time.

Invoke-SqlCmd

  • Thanks, had forgotten about SQL variables as it's been a while since I used SQL Server, and I'm a PowerShell n00b as you seem to have correctly assumed lol. But I could swear I tried option 1 and got the same result, which is why I posted here. Will test that again when I get a chance and if it indeed works, will accept your answer :-) – Kenny83 Oct 21 at 12:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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