Why does a fresh SQL Server instance with the same details as the original think that my DB backup is in the original location? 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&gt; 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
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


Change the Query value and specify the full path to the file
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