Menu
We are using Sql Server database projects to create deployment scripts from DacPac using SqlPackage.exe. We have different SQL Server Filegroups setup in various environments. When deploying we exclude Filegroups as we want objects to create in the default Filegroup. In database project settings the default Filegroup is not changed from PRIMARY.
This presents a problem when attempting to deploy to an environment where the default Filegroup is not PRIMARY because the following code is included...
ALTER DATABASE [$(DatabaseName)]
MODIFY FILEGROUP [PRIMARY] DEFAULT;
Is there a way to prevent this from generating in the deployment SQL?
330
The DAC package is in turn deployed to a test, staging or production database through an automated process or manually with a CLI or GUI tool. The . dacpac can be used to update a database with new or modified objects, to revert to a previous version of the database, or to provision an entirely new database.
The Publish DACPAC task simplifies the use of SqlPackage.exe to deploy a database from a DACPAC using a DAC Publish Profile. The great thing about DAC Publish Profiles is that they give you fine-grained control over how your database is upgraded.
SqlPackage.exe is a command-line utility that automates the following database development tasks: Version: Returns the build number of the SqlPackage application. Added in version 18.6. Extract: Creates a data-tier application (.dacpac) file containing the schema or schema and user data from a connected SQL database.
The default value is 8. Specifies the file path where the output files are generated. Specifies if sqlpackage.exe should overwrite existing files. Specifying false causes sqlpackage.exe to abort action if an existing file is encountered. Default value is True. Specifies the file path to a DAC Publish Profile.
SqlPackage.exe initiates the actions specified using the parameters, properties, and SQLCMD variables specified on the command line. Specifies the action to be performed. Specifies the token based authentication access token to use when connect to the target database.
It's important to note that DoNotAlterReplicatedObjects should be set to false, otherwise SqlPackage will fail. In a second task in the Azure Release pipeline, I analyze the generated DeployReport (e.g. the XML file) using Powershell and XPath**.
When you generate the dacpac file from SSDT in SQL Server Management Studio you can't choose you type of objects you extract to the dacpac.
But when you run sqlpackage.exe via command line there is a parameter you could use.
sqlpackage.exe your_deploy_options /p:ExcludeObjectTypes=Filegroups
This will ignore the deployment of filegroups. See this reference for the full options: https://msdn.microsoft.com/en-us/library/hh550080(v=vs.103).aspx
If you have Visual Studio, then you can import your database as a database project in VS and there remove the filegroups from the scripts, so when you deploy/compare schemas they wouldn't be compared.
184
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
