Data execution plan ended with error on DB restore

455 views Asked by At

I was trying to execute a script on power Shell which had to backup a database on one Azure server and then restore it in another server (always Azure). The script is going wrong in 2 points:

  1. When i execute the script against an existing DB, it does not drop (or kill) a database before restoring, even though there is the procedure that should do that.
  2. When i execute the script against a new created DB, i get this error:

powershell : Exception calling "ImportBacpac" with "2" argument(s): "Data plan execution At line:1 char:1 + powershell -ExecutionPolicy ByPass –File C:\Untitled1.ps1 –noexit + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (Exception calli...plan execution :String) [], RemoteException + FullyQualifiedErrorId : NativeCommandError failed with message One or more errors occurred." At C:\Untitled1.ps1:48 char:5 + $Services.ImportBacpac($Package, $restoreToDatabaseName) + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : DataException

The code that i am using is the following:

[string]$azureConnectionString = "Server=myserver.database.windows.net; User ID=myUser; Password=MyPWD; Trusted_Connection=False; Encrypt=True; Connection Timeout=30;"
[string]$azureDatabaseName = "SourceDb"
[string]$bacpacFileDropLocation = "path where to create the bacpac file"
[string]$restoreToServer = 'secondserver'
[string]$restoreToDatabaseName="DestinationDb"
[string]$restoreToConnectionString = "Server=secondserver.database.windows.net,1433; User ID=User; Password=Pwd; Trusted_Connection=False; Encrypt=True; Connection Timeout=30;"
[string]$SqlInstallationFolder = "C:\Program Files (x86)\Microsoft SQL Server"
Write-Host "Backing up Azure database '$azureDatabaseName' and restoring to database '$restoreToDatabaseName'"

$Watch = New-Object System.Diagnostics.StopWatch
$Watch.Start()
$DacAssembly = "$SqlInstallationFolder\120\DAC\bin\Microsoft.SqlServer.Dac.dll"
$smo= "C:\Program Files (x86)\Microsoft SQL Server\120\SDK\Assemblies\Microsoft.SqlServer.Smo.dll"
Add-Type -Path $DacAssembly
Add-Type -Path $smo
$Services = new-object Microsoft.SqlServer.Dac.DacServices $azureConnectionString

if ($Services -eq $null)
{ 
    Write-Host "Failed to load Sql Server Dac Services"
    exit
}

$Services.ExportBacpac($bacpacFileDropLocation, $azureDatabaseName)

if (test-path($bacpacFileDropLocation))
{      
    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
    $SMOserver = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist $restoreToServer
    Write-Host "$SMOserver.Databases[$restoreToDatabaseName]"
    Write-Host "$restoreToDatabaseName"
    if  ($SMOserver.Databases[$restoreToDatabaseName] -ne $null) 
    {  
       # $SMOserver.Databases[$restoreToDatabaseName].Drop()  
       $SMOserver.KillDatabase($restoreToDatabaseName)
   } 
    else {Write-Host "Database not dropped"
    }

    $Services = new-object Microsoft.SqlServer.Dac.DacServices $restoreToConnectionString
    Write-Host $Services
    if ($Services -eq $null)
    {
        exit
    }

    $Package =  [Microsoft.SqlServer.Dac.BacPackage]::Load($bacpacFileDropLocation)
    $Services.ImportBacpac($Package, $restoreToDatabaseName)
    $Package.Dispose()
    $Watch.Stop();
    Write-Host "Azure backup and local restore completed in "$Watch.Elapsed.ToString()
}
else
{
    Write-Host "Back up has failed!"
}

Is logical that when i execute the script on a new Db, the message "Database not dropped" shows up, but it also shows up when I execute it against an existing Db. Maybe is there something wrong in y script? I am a newbie and so is harder for me to debug such code

0

There are 0 answers