Restore azure sql database to managed instance

0
votes

I am trying to do a daily restore from azure sql database (elastic pool) into a managed instance. I have tried to export and import the bacpac file but that failed with the following error:

TITLE: Microsoft SQL Server Management Studio

Could not import package.
Warning SQL72012: The object [XTP] 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 [XTP] 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 [data_0] 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 [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 16, State 30, Line 1 Incorrect syntax near 'type'.
Error SQL72045: Script execution error. The executed script:

CREATE USER [**************]
   WITH SID = 0x6B6F4FABE3FFA848BAFB6C956D9A7E9C, TYPE = E;

I have also tried to use the automation accounts to execute powershell but that also didn't seem to support the managed instance.

How can I restore these sql databases into the managed instance?

2
sqlsql-serverazuremanaged
Well, TYPE isn't a keyword for CREATE USER, as the error is telling you. – Larnu
This was automatically generated using the Export Data-Tier Application. – Abdul Khan
I just tried to import the same bacpac file to a local instance of SQL server and it worked just fine – Abdul Khan
@AbdulKhan can you please try this tutorial: techcommunity.microsoft.com/t5/azure-database-support-blog/…? – Leon Yue
I have managed to get this to work. It is a work-around for now. 1. Create a copy of the DB and then drop all AAD users from the database before exporting – Abdul Khan

2 Answers

0
votes

Congratulations you managed to get is work:

  1. Create a copy of the DB
  2. drop all AAD users from the database before exporting

This can be beneficial to other community members. Thank you.

0
votes

Also what you can do is:

  1. rename the .bacpac file to .zip and unzip it

  2. go to model.xml file and find all entries that looks like:

    a) <Element Type="SqlRoleMembership">
b) <Element Type="SqlUser" Name="
c) <Element Type="SqlLogin" Name="
d) <Element Type="SqlExternalDataSource"

  3. after that save the model.xml and run this powershell:

    $modelXmlPath = Read-Host "PAth to model.xml" $hasher =[System.Security.Cryptography.HashAlgorithm]::Create("System.Security.Cryptography.SHA256CryptoServiceProvider") $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

  4. take the hash and substitute the actual checksum value at Origin.Xml

  5. zip everything again and import anywhere (almost)

please find reference entry:

Error Importing Azure bacpac file to local db error incorrect syntax near EXTERNAL