Restore-SqlDatabase tries to connect to the database it is trying to restore

[cairneym]

Using version 22.3.0 of the SqlServer module.

I'm trying to restore from URL using the command:

Restore-SqlDatabase -ServerInstance localhost -Database MyDB -BackupFile 'https://storageaccount.blob.core.windows.net/container/filename' -RestoreAction Log -NoRecovery -TrustServerCertificate

This is to restore to my local test instance of SQL Server 2022.

My login is a sysadmin member and default database is master. The command fails with the error message:

Restore-SqlDatabase : Failed to connect to server localhost.
At line:1 char:1
+ Restore-SqlDatabase -ServerInstance localhost -Database MyDB ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [Restore-SqlDatabase], ConnectionFailureException
    + FullyQualifiedErrorId : Microsoft.SqlServer.Management.Common.ConnectionFailureException,Microsoft.SqlServer.Management.PowerShell.RestoreSqlDatabaseCommand

When I trace the connection failure I see the following logged:

message Login failed for user 'mycomputer\localadmin'. Reason: Failed to open the explicitly specified database 'MyDB'. [CLIENT: ]

It appears that the Database parameter is being used by the connection to specify a database and as this database is still in recovery (I restored the Full backup separately) it obviously cannot connect to it.

[cairneym]

As a workaround, I was able to use

Set-Location SQLSERVER:/SQL/localhost/DEFAULT/master

Then I was able to use:

Restore-SqlDatabase -Database MyDB -BackupFile 'https://storageaccount.blob.core.windows.net/container/filename' -RestoreAction Log -NoRecovery -TrustServerCertificate

This ran successfully which seems to back up the suggestion that the -Database parameter when combined with -ServerInstance may be using the Database parameter to set the connection context.

[Matteo-T]

Thanks @cairneym - yeah, it looks indeed suspicious.

I've been a little bit busy lately, but... I'll try to take a look as soon as I can.