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:
- 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.
- 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