We can use SSMS to export data-tier application for an SQL Server database. I've been using this method for several years on my local machine.
However, to make things work under automated environments, like CI/CD environments, or some automated scheduled tasks. We usually need to do it under the command line.
Microsoft has provided a cross-platform command-line tool that can import/export DAC: sqlpackage.exe
It's written in .NET and currently in preview for Linux and macOS. For Windows, you can download and install it here: https://go.microsoft.com/fwlink/?linkid=2087429
It installed to C:\Program Files\Microsoft SQL Server\150\DAC\bin, but it doesn't add to the PATH environment variable yet. So, in order to use it, we have to manually switch to the installation directory first. Or perhaps we should configure it to PATH on a server.
To export a data-tier application, specify 3 parameters:
- /Action:{Extract|DeployReport|DriftReport|Publish|Script|Export|Import} Specifies the action to be performed. (short form /a), we will only use Export in this example.
- /SourceConnectionString:<string>Specifies a valid SQL Server/Azure connection string to the source database. If this parameter is specified it shall be used exclusively of all other source parameters. (short form /scs)
- /TargetFile:<string> Specifies a target file (i.e., a .dacpac files) to be used as the target of action instead of a database. If this parameter is used, no other target parameter shall be valid. This parameter
shall be invalid for actions that only support database targets. (short form /tf)
Example:
SqlPackage.exe /a:export /scs:"Server=(local);Database=moonglade-dev;Trusted_Connection=True;" /tf:"D:\moonglade-dev-20190520.bacpac"
And my bacpac file is there:
William
Nice article, but one point of confusion. You seem to be talking about dacpac (which contains the db structure only) and then you suddenly switch to bacpac (which is BOTH data and structure). The SSMS menu option "Extract Data Tier App" generates a dacpac. In your screenshot you highlight this option. But then in your command line call you specify the target as a bacpac.
Preeti
I am trying to export the bacpac file to a local drive but am getting errors. Error SQL 71627, which are -Authentication type property is set to a value that is not supported in Microsoft Azure SQL database v12 And IsMappedtoWindowsLogin property is set to a value that is not supported in Microsoft Azure SQL database v12
How can I solve these issue. Please help.
Cobysan
This helped me big time because I could not get it to work using SSMS UI Wizard because of the fixed timeout. I used your CMD information combined with the added /p:CommandTimeout="0" and worked like a charm!
Thank you.
Jose Marcenaro
Very helpful, works like a charm. Thanks! NOTE: in recent versions, depending on your security settings you may need to add "TrustServerCertificate=true" to your connection string to avoid a security connection error.