SqlRestore Just won't work

Question

I have the following code for restoring a database.

	try {
		Invoke-Command -Session $session -ScriptBlock {	
			param([string] $BackupFile, [string] $serverName)		
			$server = New-Object ("Microsoft.SqlServer.Management.Smo.Server") $serverName
			$server.ConnectionContext.StatementTimeout = [System.Int32]::MaxValue
			$server.ConnectionContext.Connect()
			$Restore = New-Object "Microsoft.SqlServer.Management.Smo.Restore"
			$Restore.NoRecovery = $True
			$Restore.ReplaceDatabase = $true
			$Restore.Action = "Database"
			$Restore.PercentCompleteNotification = 0
			$BackupDevice = New-Object ("Microsoft.SqlServer.Management.Smo.BackupDeviceItem") ($BackupFile, "File")
			$Restore.Devices.Add($BackupDevice) 
			$RestoreDetails = $Restore.ReadBackupHeader($server)
			$logicalFileNameList = $Restore.ReadFileList($server)

			$Restore.Database = $RestoreDetails.Rows[0]["DatabaseName"]
			foreach($row in $logicalFileNameList) {
				$RestoreDBFile = new-object("Microsoft.SqlServer.Management.Smo.RelocateFile")
				$RestoreDBFile.LogicalFileName = $row["LogicalName"]
				$RestoreDBFile.PhysicalFileName = $row["PhysicalName"]
				$Restore.RelocateFiles.Add($RestoreDBFile)
			}
			$Restore.SqlRestore($server)
			$server.ConnectionContext.DisConnect()
			write-host ("Completed the Database Restore operation on server for Database " +  $RestoreDetails.Rows[0]["DatabaseName"] + " on server $server")
		} -ArgumentList ($BackupFile, $session.ComputerName);
	}
	catch {
        $error[0]|format-list –force
    	Write-Host $_.InvocationInfo.MyCommand -ForegroundColor "red"
    	Write-Host $_.InvocationInfo.InvocationName -ForegroundColor "red"
    	Write-Host $_.InvocationInfo.PositionMessage -foregroundcolor "red"	
        write-host $_.Exception -foregroundcolor "red"		
	}

but I keep getting this error and I don't know how to solve this

0
1
Exception calling "SqlRestore" with "1" argument(s): "Restore failed for Server 'etc784993'. "
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException

For the life of me .... I cannot get this to work without making the call as  SqlRestoreAsync.... but in my case I don't want async call I want the call to e sync.

From the the restore operation works in 2 seconds without any problem what soever from powershelll.... this code just won't work!!!

---

MSDNStudent Knows not much!

Answer 1

I ran your code with a slight modification and it worked for me:

	Invoke-Command -Session $session -ScriptBlock {	
			param([string] $BackupFile, [string] $serverName)
            add-type -AssemblyName "Microsoft.SqlServer.ConnectionInfo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
            add-type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
            add-type -AssemblyName "Microsoft.SqlServer.SMOExtended, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
#Rest of code ...

Answer 2

These assemblies are already added in my powershell profile... so they are not added explicitly in my code.

get-pssnapin -registered

Add-PSSnapin SqlServerProviderSnapin100
Add-PSSnapin SqlServerCmdletSnapin100
Update-TypeData "C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\SQLProvider.Types.ps1xml"
Update-FormatData "C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\SQLProvider.Format.ps1xml"

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlEnum") | Out-Null

Can you please look at the way I am handling the exception and tell me what can I do to get more information about the error

---

MSDNStudent Knows not much!

Answer 3

SMO uses nested exceptions and there are two ways of getting at the base exception in Powershell. First the method you're using with format-list and force and second using the .NET method getbaseexception:

$_.Exception.GetBaseException().Message

You could try replacing your catch logic with the above code.

As I stated the script works for me. One issue I've seen is when loading SMO assemblies on a machine with both 2005 and 2008 installed you have to use strong names as I have done.

Does this script work outside of invoke-command?

Answer 4

I got the error message finally. thank you so much 

the error was that the mdf file on the server where the data was backed up was in d:\mydatabases\xyz.mdf

where as in the target server, in the D drive there was no d:\mydatabases directory.

it was saying something of running the command again "WITH MOVE"

in my powershell code.... how do I include this with move functionality?

---

MSDNStudent Knows not much!

Answer 5

You are doing the equivalent of WITH MOVE in your script, except you're specifying the same physical path:

$RestoreDBFile.PhysicalFileName = $row["PhysicalName"]

You'll need to change this to something like this:

$physicalName = "C:\someOtherDirectory\{0}" -f [system.io.path]::GetFileName($row["PhysicalName"])
$RestoreDB.File.PhysicalFileName = $physicalName

Answer 6

I changed my code like

$RestoreDBFile = new-object("Microsoft.SqlServer.Management.Smo.RelocateFile")            
$RestoreDBFile.LogicalFileName = $row["LogicalName"]
$fullFileName = $row["PhysicalName"].ToString()
$lastPos = $fullFileName.LastIndexOf('\')
$shortName = $fullFileName.Substring($lastPos + 1, $fullFileName.Length - $lastPos -1)            
$RestoreDBFile.PhysicalFileName = ('e:\' + $shortName)
$Restore.RelocateFiles.Add($RestoreDBFile)

But I get error

Came inside RestoreLogsOnServer
0
1
The file 'Managed Metadata' cannot be moved by this RESTORE operation.
The file 'Managed Metadata_log' cannot be moved by this RESTORE operation.
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
RESTORE LOG is terminating abnormally.
Completed the Restore Log operation on [etc784993.firmwide.corp.gs.com] for Database Abhishek Managed Metadata

---

MSDNStudent Knows not much!

Answer 7

Perhaps its a problem in your parsing logic. Try hard coding the the physical file name first.