Troubleshoot SQL Azure Bacpac Import Failures

By: Tim Smith   |   Comments   |   Related: > Azure

Problem

When importing bacpacs in SQL Azure, we sometimes see a dbo._TransactionIndex_ (GUID attached) table and when looking through our database, see that we're missing data. In some cases, we'll receive an error indicating that data failed to copy, yet sometimes we receive no error during the import, but see the table and in searching find we're missing data.

Solution

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

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

What may create confusion for some developers is that the SQL Azure database will be present with data, but as we compare and search the database, we begin to see missing data and (or) a dbo._TransactionIndex_ table that doesn't exist in the source database.

When debugging this problem, the first concern is verifying that our bacpac file is valid, as the error may occur because our bacpac file is invalid (similar to a corrupt bak file). We can verify this by testing an import into another server (or server in another subscription) of Azure-SQL and (or) importing it into SQL Server. If we do the latter (SQL Server) and the file imports without error, run DBCC CHECKDB and verify that there are no integrity violations. If using another server in Azure-SQL, verify that no dbo._TransactionIndex_ table exists, no errors were thrown, and you don't find missing data. The below code is one way that you can do a quick compare 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 you 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 weren't committed. For this reason, in PowerShell we can either (1) restore to a point in time, or (2) 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"

In these cases, 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, such as failing CHECKDB or missing records. The below function is altered version of the Import-Bacpac function, which allows us to import multiple bacpacs by location and also allows us to pass in our own connection string (which can be saved in a configuration table, if we want), whether that's 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 ""

You may come across situations where when importing a file into an Azure-SQL server, you see failures, whereas when you import the file into a test Azure-SQL server, you don't have issues. This may be related to applications running against the first server during an import. Using SQL Server Management Studio, you can see this issue in testing by calling the import function of a bacpac, refreshing the server in SSMS, and opening the database being imported. Even when it's not finished importing, you can still selecting from its tables. In one particular case, an application was running an update against a database that was importing, causing an error and preventing data from being added to the table. When importing a bacpac, all applications that run against it should be disabled, or else this may create an issue where an import fails with the database present, but missing data.

At the time of this writing, Azure charges by database not by server. This means that developers can use an empty server (or more than one) without databases that can be used if an issue arises on one server. Developers can also have different server versions for each, such as a version 11 server and a version 12 server, if needing this for either testing or emergencies. Depending on how applications are built, it may require fewer resources and time to point everything at a new server than contact support about an issue arising consistently on a particular server.

Next Steps

• Make a list of all the applications that point to a server; if running into issues during an import, either (1) temporarily rename, or (2) disable all applications.
• One regular maintenance task that you can use with SQL Server is importing bacpacs into it using the above Import-Bacpacs function, verifying their validity, then dropping them post import and verification.

About the author

Tim Smith works as a DBA and developer and also teaches Automating ETL on Udemy.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips