When you need to export a database for archiving or for moving to another platform, you can export the database schema and data to a BACPAC file. A BACPAC file is a ZIP file with an extension of BACPAC containing the metadata and data from a SQL Server database. A BACPAC file can be stored in Azure Blob storage or in local storage in an on-premises location and later imported back into Azure SQL Database or into a SQL Server on-premises installation.
Import BACPAC File to On-Premise SQL Server :
Error :
When you are try to import BACPAC File created from Azure Environment, you might encounter the following error if it consists of External Data Source Reference.
Could not import package.
Warning SQL72012: The object [AzureProd] exists in the target,
but it will not be dropped even though you selected the
‘Generate drop statements for objects that are in the target database but that
are not in the source’ check box.
Warning SQL72012: The object [AzureProd_Log] exists in the target,
but it will not be dropped even though you selected the
‘Generate drop statements for objects that are in the target database but that
are not in the source’ check box.
Error SQL72014: .Net SqlClient Data Provider: Msg 102, Level 15, State 1,
Line 1 Incorrect syntax near ‘EXTERNAL’.
Error SQL72045: Script execution error. The executed script:
CREATE EXTERNAL DATA SOURCE [DB_EXT_EDS]
WITH (
TYPE = RDBMS,
LOCATION = N’sqlserver.database.windows.net’,
DATABASE_NAME = N’AdventureWorks’,
CREDENTIAL = [DB_EXT_CRED] );
Solution :
Drop external Tables and External Data Source in Azure SQL Database and create BACPAC File again without those references.
Drop External Tables and External Data Source
IF EXISTS
(
SELECT 'x' FROM sys.external_tables)
BEGIN
DROP EXTERNAL TABLE EXT_Table1
DROP EXTERNAL TABLE EXT_Table2
DROP EXTERNAL TABLE EXT_Table3
END
IF EXISTS
(
SELECT * FROM sys.external_data_sources
WHERE name ='DB_EXT_EDS'
)
BEGIN
DROP EXTERNAL DATA SOURCE DB_EXT_EDS;
END
If you can’t recreate BACPAC without dropping the tables, you can follow these steps.
<Element Type=”SqlExternalDataSource” Name=”[BoxDataSrc]”>
<Property Name=”DataSourceType” Value=”1′′ />
<Property Name=”Location” Value=”MYAZUREServer.database.windows.net” />
<Property Name=”DatabaseName” Value=”MyAzureDb” />
<Relationship Name=”Credential”>
<Entry>
<References Name=”[SQL_Credential]” />
</Entry>
</Relationship>
</Element>
If you have multiple external data sources of this type, you will probably need to repeat step 2 for each one.
Save and close model.xml.
Now you need to re-generate the checksum for model.xml so that the bacpac doesn’t think it was tampered with (since you just tampered with it). Create a PowerShell file named computeHash.ps1 and put this code into it.
Generate Checksum
$modelXmlPath = Read-Host "model.xml file path"
$hasher = [System.Security.Cryptography.HashAlgorithm]:
:Create("System.Security.Cryptography.SHA256Crypt oServiceProvider")
$fileStream = new-object System.IO.FileStream ` -ArgumentList
@($modelXmlPath, [System.IO.FileMode]::Open)
$hash = $hasher.ComputeHash($fileStream)
$hashString = "" Foreach ($b in $hash) { $hashString += $b.ToString("X2") }
$fileStream.Close() $hashString
Run the PowerShell script and give it the filepath to your unzipped and edited model.xml file. It will return a checksum value.
Copy the checksum value, then open up Origin.xml and replace the existing checksum, toward the bottom on the line that looks like this:
<Checksum Uri=”/model.xml”>9EA0F06B282G4F42955C78A98822A31AA0ED0225CB131B
8759379055A482D0 1G</Checksum>
Save and close Origin.xml, then select all the files and put them into a new zip file and rename the extension to bacpac.
Now you can use this new bacpac to import the database without getting the error.
Share this:
CloudIQ is a leading Cloud Consulting and Solutions firm that helps businesses solve today’s problems and plan the enterprise of tomorrow by integrating intelligent cloud solutions. We help you leverage the technologies that make your people more productive, your infrastructure more intelligent, and your business more profitable.
LATEST THINKING
INDIA
Chennai One IT SEZ,
Module No:5-C, Phase ll, 2nd Floor, North Block, Pallavaram-Thoraipakkam 200 ft road, Thoraipakkam, Chennai – 600097
© 2023 CloudIQ Technologies. All rights reserved.
Get in touch
Please contact us using the form below
USA
INDIA