0

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?

Joseph Xu
  • 5,607
  • 2
  • 5
  • 15
Abdul Khan
  • 11
  • 1
  • 1
  • 3
  • Well, `TYPE` isn't a keyword for [`CREATE USER`](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-user-transact-sql?view=sql-server-ver15), as the error is telling you. – Thom A Aug 25 '20 at 13:27
  • This was automatically generated using the Export Data-Tier Application. – Abdul Khan Aug 25 '20 at 14:18
  • I just tried to import the same bacpac file to a local instance of SQL server and it worked just fine – Abdul Khan Aug 25 '20 at 14:47
  • @AbdulKhan can you please try this tutorial: https://techcommunity.microsoft.com/t5/azure-database-support-blog/how-to-migrate-azure-sql-database-to-azure-sql-managed-instance/ba-p/369182? – Leon Yue Aug 26 '20 at 07:01
  • 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 Aug 27 '20 at 14:58
  • Hi @AbdulKhan, congratulations! Thank you for sharing the workaround for us. I help you post it as the answer. Please accept it as answer( click on the check mark beside the answer to toggle it from greyed out to filled in.). This can be beneficial to other community members. Thank you. – Leon Yue Aug 28 '20 at 01:36

2 Answers2

0

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.

Leon Yue
  • 15,693
  • 1
  • 11
  • 23
0

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

jlsfernandez
  • 160
  • 3
  • 16