Using SQLPackage to import or export SQL Server and Azure SQL DB

Published 03-13-2019 06:38 PM 18.7K Views
Microsoft

First published on MSDN on Jan 31, 2017

Purpose:


Explain how to easily and quickly use SQLPackage to import or export your SQL Server and 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\<Version>\DAC\bin


* Change the version to match the installed version. (120-SQL2014, 130-SQL2016, 140-Recent)

* Change the drive letter if you installed it to a 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 (Gen5 General Purpose with 2 vCore):

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 import its recommended setting the service tier during the import, this help import process to run faster.

 

    • Note that the database can be created prior to import, the only condition is that the database will be empty. this allows you to set database properties before import starts.

 

    • For large database export you might want to set the temp folder location, as by default it will use C: drive. 

      you may run the following commands on your command line window before executing SQLPackage.

      SET TEMP={path to your temp folder}
      SET TMP={path to your temp folder}​
    • if operation logs is needed please add the below parameter to get the logs for the operation:
/DF:<FullPathFilename>

 

    • When exporting from the 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 the copied database. ( learn how to copy Azure SQL DB ) (thanks to ErikEJ for his comment)


Full documentation for SQLPackage.exe

1 Comment
New Contributor

OK, now how to connect using my AAD credentials and MFA enabled (kind of the standard access requirements for most organizations)?

%3CLINGO-SUB%20id%3D%22lingo-sub-368861%22%20slang%3D%22en-US%22%3EUsing%20SQLPackage%20to%20import%20or%20export%20SQL%20Server%20and%20Azure%20SQL%20DB%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-368861%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSTRONG%3E%20First%20published%20on%20MSDN%20on%20Jan%2031%2C%202017%20%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CH2%20id%3D%22toc-hId-1737180506%22%20id%3D%22toc-hId-1737180506%22%20id%3D%22toc-hId-1678997630%22%3EPurpose%3A%3C%2FH2%3E%0A%3CP%3E%3CBR%20%2F%3EExplain%20how%20to%20easily%20and%20quickly%20use%20SQLPackage%20to%20import%20or%20export%20your%20SQL%20Server%20and%20Azure%20SQL%20Database.%3C%2FP%3E%0A%3CH2%20id%3D%22toc-hId--814976455%22%20id%3D%22toc-hId--814976455%22%20id%3D%22toc-hId--873159331%22%3ELocating%20the%20SQLPackage.exe%3A%3C%2FH2%3E%0A%3CP%3E%3CBR%20%2F%3EThe%20SQLPackage.exe%20is%20part%20of%20the%20DacFramework%20which%20installed%20with%20SSMS%20or%20with%20SQL%20Server%20Data%20Tools%20%3CBR%20%2F%3E%3CBR%20%2F%3EOr%20you%20can%20download%20only%20the%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Ftools%2Fsqlpackage-download%3Fview%3Dsql-server-2017%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EDacFramework%20%3C%2FA%3Eif%20you%20do%20not%20have%20already%20the%20management%20tools%20%3CBR%20%2F%3E%3CBR%20%2F%3EOnce%20the%20DacFramework%20%2F%20SSMS%20%2F%20SSDT%20is%20installed%20you%20can%20locate%20the%20SQLPackage.exe%20in%20this%20path%3C%2FP%3E%0A%3CBLOCKQUOTE%3EC%3A%5CProgram%20Files%20(x86)%5CMicrosoft%20SQL%20Server%5C%3CVERSION%3E%5CDAC%5Cbin%3C%2FVERSION%3E%3C%2FBLOCKQUOTE%3E%0A%3CP%3E%3CBR%20%2F%3E*%20Change%20the%20version%20to%20match%20the%20installed%20version.%20(120-SQL2014%2C%20130-SQL2016%2C%20140-Recent)%20%3CBR%20%2F%3E%3CBR%20%2F%3E*%20Change%20the%20drive%20letter%20if%20you%20installed%20it%20to%20a%20different%20location%3C%2FP%3E%0A%3CH2%20id%3D%22toc-hId-927833880%22%20id%3D%22toc-hId-927833880%22%20id%3D%22toc-hId-869651004%22%3EExport%20from%20Azure%20SQL%20DB%20to%20bacpac%20file%3A%3C%2FH2%3E%0A%3CP%3E%3CBR%20%2F%3Eto%20export%20database%20from%20your%20Azure%20SQL%20DB%20to%20bacpac%20file%20use%20this%20command%3A%20%3CBR%20%2F%3E%3CCODE%3E%0A%20%20%20%3CEM%3E%0A%20%20%20%20sqlpackage.exe%20%2FAction%3AExport%20%2Fssn%3Atcp%3A%3CSERVERNAME%3E.database.windows.net%2C1433%20%2Fsdn%3A%3CDATABASENAME%3E%20%2Fsu%3A%3CUSERNAME%3E%20%2Fsp%3A%3CPASSWORD%3E%20%2Ftf%3A%3CTARGETFILE%3E%20%2Fp%3AStorage%3DFile%0A%20%20%20%20%3CBR%20%2F%3E%0A%20%20%20%3C%2FTARGETFILE%3E%3C%2FPASSWORD%3E%3C%2FUSERNAME%3E%3C%2FDATABASENAME%3E%3C%2FSERVERNAME%3E%3C%2FEM%3E%0A%20%20%3C%2FCODE%3E%3C%2FP%3E%0A%3CH3%20id%3D%22toc-hId--1820836586%22%20id%3D%22toc-hId--1820836586%22%20id%3D%22toc-hId--1879019462%22%3Eexample%3A%3C%2FH3%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CBLOCKQUOTE%3Esqlpackage.exe%20%2FAction%3AExport%20%2Fssn%3Atcp%3AMyOwnServer.database.windows.net%2C1433%20%2Fsdn%3AAdventureWorks%20%2Fsu%3AAdminUser%20%2Fsp%3AAdminPassword1%20%2Ftf%3AC%3A%5CTemp%5CAW.bacpac%20%3CEM%3E%2Fp%3AStorage%3DFile%3C%2FEM%3E%20%3C%2FBLOCKQUOTE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CH2%20id%3D%22toc-hId-118487254%22%20id%3D%22toc-hId-118487254%22%20id%3D%22toc-hId-60304378%22%3EImport%20from%20bacpac%20file%20to%20Azure%20SQL%20DB%3A%3C%2FH2%3E%0A%3CP%3E%3CBR%20%2F%3Eto%20import%20database%20from%20bacpac%20file%20to%20your%20Azure%20SQL%20DB%20use%20this%20command%3A%20%3CBR%20%2F%3E%3CCODE%3E%0A%20%20%20%3CEM%3E%0A%20%20%20%20sqlpackage.exe%20%2FAction%3AImport%20%2Ftsn%3Atcp%3A%3CSERVERNAME%3E.database.windows.net%2C1433%20%2Ftdn%3A%3CTARGETDATABASENAME%3E%20%2Ftu%3A%3CUSERNAME%3E%20%2Ftp%3A%3CPASSWORD%3E%20%2Fsf%3A%3CPATH%20to%3D%22%22%20bacpac%3D%22%22%20file%3D%22%22%3E%20%2Fp%3ADatabaseEdition%3DPremium%20%2Fp%3ADatabaseServiceObjective%3DP4%20%2Fp%3AStorage%3DFile%0A%20%20%20%20%3CBR%20%2F%3E%0A%20%20%20%3C%2FPATH%3E%3C%2FPASSWORD%3E%3C%2FUSERNAME%3E%3C%2FTARGETDATABASENAME%3E%3C%2FSERVERNAME%3E%3C%2FEM%3E%0A%20%20%3C%2FCODE%3E%3C%2FP%3E%0A%3CH3%20id%3D%22toc-hId-1664784084%22%20id%3D%22toc-hId-1664784084%22%20id%3D%22toc-hId-1606601208%22%3Eexample%3A%3C%2FH3%3E%0A%3CP%3E%3CBR%20%2F%3Eimport%20database%20to%20default%20target%20service%20tier%20(Gen5%20General%20Purpose%20with%202%20vCore)%3A%20%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%0A%3CBLOCKQUOTE%3Esqlpackage.exe%20%2FAction%3AImport%20%2Ftsn%3Atcp%3AMyServer.database.windows.net%2C1433%20%2Ftdn%3AAdventureWorks%20%2Ftu%3AAdminUser%20%2Ftp%3AAdminPassword1%20%2Fsf%3AC%3A%5Ctemp%5CAW.bacpac%20%3CEM%3E%2Fp%3AStorage%3DFile%3C%2FEM%3E%20%3C%2FBLOCKQUOTE%3E%0A%3CP%3E%3CBR%20%2F%3Eimport%20database%20and%20set%20target%20service%20tier%20to%20P2%3C%2FP%3E%0A%3CBLOCKQUOTE%3Esqlpackage.exe%20%2FAction%3AImport%20%2Ftsn%3Atcp%3AMyServer.database.windows.net%2C1433%20%2Ftdn%3AAdventureWorks%20%2Ftu%3AAdminUser%20%2Ftp%3AAdminPassword1%20%2Fsf%3AC%3A%5Ctemp%5CAW.bacpac%20%2Fp%3ADatabaseEdition%3DPremium%26nbsp%3B%2Fp%3ADatabaseServiceObjective%3DP2%20%3CEM%3E%2Fp%3AStorage%3DFile%3C%2FEM%3E%20%3C%2FBLOCKQUOTE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CH3%20id%3D%22toc-hId--887372877%22%20id%3D%22toc-hId--887372877%22%20id%3D%22toc-hId--945555753%22%3ERemarks%3A%3C%2FH3%3E%0A%3CP%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%0A%3CUL%3E%0A%3CUL%3E%0A%3CLI%3E%20%3CEM%3E%2Fp%3AStorage%3DFile%20%3A%20is%20used%20to%20redirect%20the%20%3C%2FEM%3Ebacking%20storage%20for%20the%20schema%20model%20used%20during%20extraction%2C%20this%20helpful%20with%20large%20databases%20that%20may%20cause%20out-of-memory%20exception%20if%20the%20default%20memory%20location%20is%20used.%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3C%2FUL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CUL%3E%0A%3CUL%3E%0A%3CLI%3ENote%20that%20for%20large%20databases%20import%20its%20recommended%20setting%20the%20service%20tier%20during%20the%20import%2C%20this%20help%20import%20process%20to%20run%20faster.%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3C%2FUL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CUL%3E%0A%3CUL%3E%0A%3CLI%3ENote%20that%20the%20database%20can%20be%20created%20prior%20to%20import%2C%20the%20only%20condition%20is%20that%20the%20database%20will%20be%20empty.%20this%20allows%20you%20to%20set%20database%20properties%20before%20import%20starts.%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3C%2FUL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CUL%3E%0A%3CUL%3E%0A%3CLI%3EFor%20large%20database%20export%20you%20might%20want%20to%20set%20the%20temp%20folder%20location%2C%20as%20by%20default%20it%20will%20use%20C%3A%20drive.%26nbsp%3B%0A%3CP%3Eyou%20may%20run%20the%20following%20commands%20on%20your%20command%20line%20window%20before%20executing%20SQLPackage.%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-bash%22%3E%3CCODE%3ESET%20TEMP%3D%7Bpath%20to%20your%20temp%20folder%7D%0ASET%20TMP%3D%7Bpath%20to%20your%20temp%20folder%7D%E2%80%8B%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3C%2FUL%3E%0A%3CUL%3E%0A%3CUL%3E%0A%3CLI%3Eif%20operation%20logs%20is%20needed%20please%20add%20the%20below%20parameter%20to%20get%20the%20logs%20for%20the%20operation%3A%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3C%2FUL%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-bash%22%3E%3CCODE%3E%2FDF%3A%3CFULLPATHFILENAME%3E%3C%2FFULLPATHFILENAME%3E%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CUL%3E%0A%3CUL%3E%0A%3CLI%3E%0A%3CDIV%3EWhen%20exporting%20from%20the%20active%20database%2C%20the%20database%20may%20not%20be%20transactional%20consistent%20as%20this%20process%20go%20object%20by%20object.%20If%20transactional%20consistent%20is%20needed%20it's%20recommended%20to%20export%20from%20the%20copied%20database.%20(%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fsql-database%2Fsql-database-copy-portal%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20learn%20how%20to%20copy%20Azure%20SQL%20DB%20%3C%2FA%3E%20)%26nbsp%3B(thanks%20to%20%3CA%20href%3D%22https%3A%2F%2Fsocial.msdn.microsoft.com%2Fprofile%2FErikEJ%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20%3CEM%3E%20ErikEJ%20%3C%2FEM%3E%20%3C%2FA%3E%20for%20his%20comment)%3C%2FDIV%3E%0A%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3C%2FUL%3E%0A%3CP%3E%3CBR%20%2F%3EFull%20documentation%20for%20%3CA%20href%3D%22https%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Fhh550080(vs.103).aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20SQLPackage.exe%3C%2FA%3E%20%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-368861%22%20slang%3D%22en-US%22%3E%3CP%3EFirst%20published%20on%20MSDN%20on%20Jan%2031%2C%202017%20Purpose%3AExplain%20how%20to%20easily%20and%20quickly%20use%20SQLPackage%20to%20import%20or%20export%20your%20SQL%20Server%20and%20Azure%20SQL%20Database.%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2278906%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20SQLPackage%20to%20import%20or%20export%20SQL%20Server%20and%20Azure%20SQL%20DB%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2278906%22%20slang%3D%22en-US%22%3E%3CP%3EOK%2C%20now%20how%20to%20connect%20using%20my%20AAD%20credentials%20and%20MFA%20enabled%20(kind%20of%20the%20standard%20access%20requirements%20for%20most%20organizations)%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
Version history
Last update:
‎Dec 16 2020 12:35 AM
Updated by: