SQL Azure Import Bacpac Failures – Troubleshooting method

The error, SQL Azure Import Bacpac Failures may occur due to an invalid bacpac file.

As part of our Server Management Services, we assist our customers with several such errors on a daily basis.

Today, let us discuss how to troubleshoot SQL Azure Import Bacpac Failures.

SQL Azure Import Bacpac Failures

When importing bacpacs in SQL Azure, we sometimes see a dbo._TransactionIndex_ table. and when looking through our database, see that we are missing data.

In some cases, we will receive an error indicating that data failed to copy, yet sometimes we receive no error during the import.

When importing SQL Azure bacpacs, we may see some of the following errors:

• Exception calling “ImportBacpac” with “2” argument(s): “Data plan execution failed with message One or more errors occurred.”
• Exception calling “ImportBacpac” with “2” argument(s): “An error occurred during deployment plan generation. Deployment cannot continue.”
• [An error with T-SQL in it, usually showing a foreign key violation or transactional blocking].

What may create confusion is that the SQL Azure database will be present with data. However, as we compare and search the database, we begin to see missing data and (or) a dbo._TransactionIndex_ table that does not exist in the source database.

Solution for SQL Azure Import Bacpac Failures

While debugging this problem, the first concern is to verify that our bacpac file is valid. The error may occur if it is invalid. We can verify this by testing an import into another server of Azure-SQL and (or) importing it into SQL Server.

If we do the latter, run DBCC CHECKDB and verify that there are no integrity violations. If we use another server in Azure-SQL, verify that no dbo._TransactionIndex_ table exists, no errors were thrown, and no missing data.

With the below code we can do a quick comparison using the counts of rows in each table:

SELECT ‘SELECT COUNT(*) FROM ‘ + QUOTENAME(TABLE_SCHEMA) + ‘.’ + QUOTENAME(TABLE_NAME)
FROM INFORMATION_SCHEMA.COLUMNS
ORDER BY TABLE_NAME
 
If we find that row counts don’t match, CHECKDB fails, or errors during an import, the bacpac file is invalid.
 
One of the possible reasons is that during the export, transactions fail to commit.
 
For this reason, in PowerShell, we can either
 
 
restore to a point in time, or
 
copy the database on the same server, or to another server.
 
 
### The below performs a non-continuous database copy
Start-AzureSqlDatabaseCopy -ServerName “OurAzureServer” -DatabaseName “OurDatabase” -PartnerDatabase “OurDatabase_Copied”

### The below restores a databse to the UTC point in time 8AM
Start-AzureSqlDatabaseRestore -SourceDatabase “OurDatabase” -TargetDatabaseName “OurDatabase_Copied” -PointInTime “2015-04-01 08:00:00”
 
Here, we can use the Get-Bacpacs function to obtain a new file from the restored/copied databases and verify it. These steps assume that the source database of the bacpac has no issues.
 
The below function is an altered version of the Import-Bacpac function. It allows us to import multiple bacpacs by location and also allows us to pass in our own connection string, whether that is a local SQL Server environment or an Azure-SQL environment.
 
Function Import-BacBacs {
Param(
[string]$server
, [string]$daclibrary
, [string]$location
, [string]$setscon
)
Process
{
Add-Type -Path $daclibrary

if ($setscon -eq $null)
{
$setscon = “Data Source=$server;Initial Catalog=master;Connection Timeout=0;Integrated Security=true;”
}

$d_impbac = New-Object Microsoft.SqlServer.Dac.DacServices $setscon

$allbacs = Get-ChildItem $location -Filter *bacpac

foreach ($bac in $allbacs)
{
$bcfile = $bac.FullName
$name = $bac.BaseName

try
{
$d_loadbac = [Microsoft.SqlServer.Dac.BacPackage]::Load($bcfile)
$d_impbac.ImportBacpac($d_loadbac, $name)
}
catch [Exception]
{
Write-Warning $name
Write-Warning $_
}
}
}
}

### Location may differ relative to SQL Server version installed
$dac = “C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\Microsoft.SqlServer.Dac.dll”

Import-BacBacs -server “OurAzureServer” -daclibrary $dac -location “E:\Bacpacs\” -setscon “”
 
We may come across situations where when importing a file into an Azure-SQL server, we see failures. This may be related to applications running against the first server during an import.
 
Using SQL Server Management Studio, we can see this issue in testing. We need to use the import function of a bacpac, refresh the server in SSMS, and open the database being imported. Even while the import is to finish, we can select from its tables.
 
When importing a bacpac, all applications that run against it should be disabled. Otherwise, this may create an issue where an import fails with the database present, but missing data.
 
Make a list of all the applications that point to a server; if running into any issue during an import, either
 
 
temporarily rename, or
 
disable all applications.
 
 
One maintenance task that we can use with SQL Server is importing bacpacs into it using the above Import-Bacpacs function.
 
[The error continues to prevail? We are here for you!]
 
 
 
Conclusion
 
To conclude, the error, SQL Azure Import Bacpac Failures generally occur due to an invalid bacpac file. Today, we saw an effective way our Support Engineers employ, in order to fix this error.
 
  
Related posts:
 
How to deploy DACPAC with SQL Server Management Studio
 
How to change SQL Server database Autogrowth settings
 
SQL server database last restore time – How to find it
 
How to Identify and fix Orphaned users in SQL Server?