0

I have a Powershell function that is calling "Restore-SqlDatabase" from the SqlPs module and sends parameters to the function via splatting. I'm trying to figure out why it's not working the way I expect it to. Here's my code:

$SqlParams = @{"ServerInstance"="'$ServerName'";
    "Database"="'$DatabaseName'";
    "BackupFile"="'$BackupFile'";}

if($Credential -ne $null) {
    $SqlParams.Add("SqlCredential", $Credential)
}

if($ReplaceDatabase) {
    $SqlParams.Add("ReplaceDatabase", $null)
}

try { $PathResult = Test-Path -Path $RestorePath } catch { $PathResult = $False }

if($PathResult) {
    Write-Verbose "RestorePath exists, using: $RestorePath"

    $RelocateData = New-Object 'Microsoft.SqlServer.Management.Smo.RelocateFile, Microsoft.SqlServer.SmoExtended, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'
    $RelocateData.LogicalFileName = $DatabaseName
    $RelocateData.PhysicalFileName = $(Join-Path -Path $RestorePath -ChildPath "$DatabaseName.mdf")

    $RelocateLog = New-Object 'Microsoft.SqlServer.Management.Smo.RelocateFile, Microsoft.SqlServer.SmoExtended, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'
    $RelocateLog.LogicalFileName = "$DatabaseName`_log"
    $RelocateLog.PhysicalFileName = $(Join-Path -Path $RestorePath -ChildPath "$DatabaseName`_log.ldf")

    $RelocateFile = @($RelocateData,$RelocateLog)
    $SqlParams.Add("RelocateFile", $RelocateFile)
}

try{
    Write-Verbose "Using the following parameters:"
    $paramString = $SqlParams | Out-String
    Write-Verbose $paramString 
    Restore-SqlDatabase @SqlParams
} catch { 
    Write-Error $_ 
    Write-Output "Restore failed..."
    return
}
Write-Output "Database successfully restored!"

When I run this, it fails to connect to the database. Yet, when I run the command directly in the terminal, using a "param" hashtable and the same parameter values, it works exactly as expected.

Any tips for me?

Ethan Eiter
  • 101
  • 9
  • what is the exact error you get , what is the content of the `$Error` list ? are there any missing / not specified parameters reported by `Restore-SqlDatabase` ? – Ronald Rink 'd-fens' Mar 15 '17 at 17:40
  • I am now getting the following error: `Cannot validate argument on parameter 'DatabaseObject'. The argument is null or empty. Provide an argument that is not null or empty, and then try the command again. At line:1 char:1` That is the only thing of importance in the `$Error` list – Ethan Eiter Mar 15 '17 at 22:35
  • I tried removing the single quotes, didn't help. `$SqlParams = @{"ServerInstance" = $ServerName; "Database" = $DatabaseName; "BackupFile" = $BackupFile; }` – Ethan Eiter Mar 15 '17 at 22:36

1 Answers1

0

Here's some secret sauce.

Did this before the Restore-SqlDatabase call:

SQLCMD -S $ServerName -d master -Q "Alter Database [$DatabaseName] SET SINGLE_USER With ROLLBACK IMMEDIATE"

And did this after the Restore-SqlDatabase call:

SQLCMD -S $ServerName -d master -Q "Alter Database [$DatabaseName] SET MULTI_USER WITH ROLLBACK IMMEDIATE"

All seems to work fine now.

Ethan Eiter
  • 101
  • 9