First published on MSDN on Jan 28, 2019
The latest version of SQLPackage supports migration to Azure SQL Managed Instance (MI) from multiple SQL sources such as Azure SQL Database.
Export:
Following the below steps, you can export a SQL database using the SQLPackage command-line utility. If possible, use a workstation that has access to the Azure SQL Database and the MI to avoid having to move the exported bacpac to a workstation that has access to your destination MI.
In the above command, we will export the database “dbname” from server “servername” to a local file named “backup150.bacpac”. You will want to adjust these values to match your setup.
Import:
If the environment that contains the exported bacpac file does not have access to the destination MI, move the exported bacpac file “backup150.bacpac” to an environment that can access your Azure SQL MI.
Following the below steps, you can import the bacpac into an Azure SQL Managed Instance using the same SQLPackage utility for export. If you have already done step 1 and 2 on your current environment from the export above, please skip to step 3 to run the import.
In the above command, we will import the database “dbname” into your destination MI “ManagedInstancename” from the bacpac named “backup150.bacpac”. You will want to adjust these values to match your setup.
Please note that older DACfx versions than “18.0” do not support migration to Azure SQL Managed Instance and to ensure latest compatibility, please make sure that you are using the latest version of SQLPackage. Using older version could cause errors such as listed below:
*** Error importing database:Could not import package.
Unable to connect to master or target server ‘database name’. You must have a user with the same password in master or target server.
Or
*** Error importing database:Could not import package.
Warning SQL0: A project which specifies Microsoft Azure SQL Database v12 as the target platform may experience compatibility issues with SQL Server 2014.
Error SQL72014: .Net SqlClient Data Provider: Msg 41918, Level 16, State 1, Line 2 Specifying files and filegroups in CREATE DATABASE statement is not supported on SQL Database Managed Instance.
Error SQL72045: Script execution error. The executed script:
CREATE DATABASE [$(DatabaseName)]
ON
PRIMARY(NAME = [$(DatabaseName)], FILENAME = N'$(DefaultDataPath)$(DefaultFilePrefix)_Primary.mdf')
LOG ON (NAME = [$(DatabaseName)_log], FILENAME = N'$(DefaultLogPath)$(DefaultFilePrefix)_Primary.ldf') COLLATE SQL_Latin1_General_CP1_CI_AS;
The latest version of SQLPackage supports migration to Azure SQL Managed Instance (MI) from multiple SQL sources such as Azure SQL Database.
Export:
Following the below steps, you can export a SQL database using the SQLPackage command-line utility. If possible, use a workstation that has access to the Azure SQL Database and the MI to avoid having to move the exported bacpac to a workstation that has access to your destination MI.
- Download and run the DacFramework.msi installer for Windows.
-
Open a new Command Prompt window, and run the following command
cd C:\Program Files\Microsoft SQL Server\150\DAC\bin -
Run the following command to export the SQL database:
sqlpackage.exe /a:Export /SourceServerName:servername.database.windows.net /SourceDatabaseName:dbname /SourceUser:username /SourcePassword:password /TargetFile:C:\Users\user\Desktop\backup150.bacpac
In the above command, we will export the database “dbname” from server “servername” to a local file named “backup150.bacpac”. You will want to adjust these values to match your setup.
Import:
If the environment that contains the exported bacpac file does not have access to the destination MI, move the exported bacpac file “backup150.bacpac” to an environment that can access your Azure SQL MI.
Following the below steps, you can import the bacpac into an Azure SQL Managed Instance using the same SQLPackage utility for export. If you have already done step 1 and 2 on your current environment from the export above, please skip to step 3 to run the import.
- Download and run the DacFramework.msi installer for Windows.
-
Open a new Command Prompt window, and run the following command
cd C:\Program Files\Microsoft SQL Server\150\DAC\bin -
Run the following command to import into the Managed Instance
sqlpackage.exe /a:Import /TargetServerName:ManagedInstancename.appname.database.windows.net /TargetDatabaseName:dbname /TargetUser:username /TargetPassword:password /SourceFile:C:\Users\user\Desktop\backup150.bacpac
In the above command, we will import the database “dbname” into your destination MI “ManagedInstancename” from the bacpac named “backup150.bacpac”. You will want to adjust these values to match your setup.
Please note that older DACfx versions than “18.0” do not support migration to Azure SQL Managed Instance and to ensure latest compatibility, please make sure that you are using the latest version of SQLPackage. Using older version could cause errors such as listed below:
*** Error importing database:Could not import package.
Unable to connect to master or target server ‘database name’. You must have a user with the same password in master or target server.
Or
*** Error importing database:Could not import package.
Warning SQL0: A project which specifies Microsoft Azure SQL Database v12 as the target platform may experience compatibility issues with SQL Server 2014.
Error SQL72014: .Net SqlClient Data Provider: Msg 41918, Level 16, State 1, Line 2 Specifying files and filegroups in CREATE DATABASE statement is not supported on SQL Database Managed Instance.
Error SQL72045: Script execution error. The executed script:
CREATE DATABASE [$(DatabaseName)]
ON
PRIMARY(NAME = [$(DatabaseName)], FILENAME = N'$(DefaultDataPath)$(DefaultFilePrefix)_Primary.mdf')
LOG ON (NAME = [$(DatabaseName)_log], FILENAME = N'$(DefaultLogPath)$(DefaultFilePrefix)_Primary.ldf') COLLATE SQL_Latin1_General_CP1_CI_AS;
Updated Mar 14, 2019
Version 2.0
Bashar-MSFT
Microsoft
Microsoft
