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.

[avdmeer]

Hi Matteo, I seem to be hitting the same issue when restoring an on-premise database. Did you have any time to debug and fix this issue yet ? Or provide a workaround ?
The above workaround of: Set-Location SQLSERVER:/SQL/$env:COMPUTERNAME/DEFAULT/databases/master
still leads to: Failed to connect to server YYY.
and in the ERRORLOG:
Reason: Failed to open the explicitly specified database 'XXX' (with XXX being the db name I am trying to restore.
I tried the same code with SQLServer module 22.2.0 and it worked fine in that case.

[jherschel77]

I am seeing this bug too in a Runbook we are running. I do not think the workaround will work since we are not running on the server.

[rathole]

Seeing same issue as reported by @cairneym. I had to roll back to module version 22.2.0.

[Matteo-T]

Ok... problem understood. It's indeed a regression from 22.2 I introduced in 22.3.

I'm working on a fix...

[andrewcurrie1984]

For a workaround I have done the following:

Import-Module SqlServer
[int]$sqlServerModule = (Get-Module SqlServer -ListAvailable).Version.Major

#Inputs
$SQLInstance = <MySQLInstanceHere>
$Database = <MyDatabaseHere>
$BackupFile = <PathToYourBackupFileHere>
#

if ($sqlServerModule -ge 23) {
	Restore-SqlDatabase -ServerInstance $SQLInstance -Database $Database -BackupFile $BackupFile -NoRecovery -Encrypt Optional
} else {
	Restore-SqlDatabase -ServerInstance $SQLInstance -Database $Database -BackupFile $BackupFile -NoRecovery
}

[Matteo-T]

This was fixed in 22.4.5.1