Freigeben über

Using SQLPackage to import or export Azure SQL DB

  • Artikel

Purpose:

Explain how to easily and quickly use SQLPackage to import or export your Azure SQL Database.

Locating the SQLPackage.exe:

The SQLPackage.exe is part of the DacFramework which installed with SSMS or with SQL Server Data Tools

Or you can download only the DacFramework if you do not have already the management tools

Once the DacFramework / SSMS / SSDT is installed you can locate the SQLPackage.exe in this path

C:\Program Files (x86)\Microsoft SQL Server\130\DAC\bin

* Change the drive letter if you installed it to different location.

Export from Azure SQL DB to bacpac file:

to export database from your Azure SQL DB to bacpac file use this command:

 sqlpackage.exe /Action:Export /ssn:tcp:<ServerName>.database.windows.net,1433 /sdn:<DatabaseName> /su:<UserName> /sp:<Password> /tf:<TargetFile> /p:Storage=File

example:

sqlpackage.exe /Action:Export /ssn:tcp:MyOwnServer.database.windows.net,1433 /sdn:AdventureWorks /su:AdminUser /sp:AdminPassword1 /tf:C:\Temp\AW.bacpac /p:Storage=File

Import from bacpac file to Azure SQL DB:

to import database from bacpac file to your Azure SQL DB use this command:

 sqlpackage.exe /Action:Import /tsn:tcp:<ServerName>.database.windows.net,1433 /tdn:<TargetDatabaseName> /tu:<UserName> /tp:<Password> /sf:<Path to bacpac file> /p:DatabaseEdition=Premium /p:DatabaseServiceObjective=P4 /p:Storage=File

example:

import database to default target service tier S0:

sqlpackage.exe /Action:Import /tsn:tcp:MyServer.database.windows.net,1433 /tdn:AdventureWorks /tu:AdminUser /tp:AdminPassword1 /sf:C:\temp\AW.bacpac /p:Storage=File

import database and set target service tier to P2

sqlpackage.exe /Action:Import /tsn:tcp:MyServer.database.windows.net,1433 /tdn:AdventureWorks /tu:AdminUser /tp:AdminPassword1 /sf:C:\temp\AW.bacpac /p:DatabaseEdition=Premium /p:DatabaseServiceObjective=P2 /p:Storage=File

Remarks:

  • /p:Storage=File : is used to redirect the backing storage for the schema model used during extraction, this helpful with large databases that may cause out-of-memory exception if the default memory location is used.

  • Note that for large databases its recommended to set the service tier during the import, this help import process to run faster.

  • When exporting from active database, the database may not be transactional consistent as this process go object by object. If transactional consistent is needed it's recommended to export from copied database. (learn how to copy Azure SQL DB)

    (thanks to ErikEJ for his comment)

Full documentation for SQLPackage.exe

Comments

  • Anonymous
    January 31, 2017
    Note that sqlpackage does not export a transactionally consistent package, so a good approach is to extract from a database copy.
    • Anonymous
      January 31, 2017
      The comment has been removed
  • Anonymous
    February 07, 2017
    HiWhat is the size limit of DB can be?
    • Anonymous
      February 26, 2017
      can you be more specific?