Opinions expressed on this site are solely those of Kendra Little of Catalyze SQL, LLC. Content policy: Short excerpts of blog posts (3 sentences) may be republished, but longer excerpts and artwork cannot be shared without explicit permission.
By Kendra Little on February 13, 2025
SQL Server’s free state-based version control tooling was introduced under the “Data Dude” brand, then became known as “SQL Server Data Tools” (SSDT). Its extension for the (now dying) Azure Data Studio IDE is called “SQL Database Projects.” If you need to find documentation, you often need to know to search for specific component names like SQLPackage.exe, which is a command line utility used to deploy SSDT Projects AKA SQL Database Projects.
Database Projects control more than objects inside the database
By default, deploying a SQL Database Project with SQLPackage.exe doesn’t only deploy the schema of the tables, procedures, and functions in the project. It will also deploy select attributes of SQL Server databases themselves, things like page verification settings, whether snapshot isolation is enabled, and some Query Store settings. (If there’s a complete list of the database properties that are version controlled with SQL Database Projects, I haven’t found it.)
Sometimes you don’t want to overwrite database properties on your target databases
For various reasons, you may not always want your project to deploy database settings everywhere. Perhaps you deploy your project to many single-tenant databases and you are gradually rolling out changes to settings across your environment, for example.
In this case, I’ve found that running SQLPackage.exe with the /p:ScriptDatabaseOptions=False parameter seems to work: when this is set, sqlpackage.exe won’t overwrite database properties that are controlled by the database project but vary from their definition in source.
Avoid ExcludeObjectType(s) for this
But beware: there are some other documented options like /p:ExcludeObjectType:"DatabaseOptions" which do NOT work for this. I haven’t been able to identify if setting that property, or using the similar ExcludeObjectTypes, has any effect at all, or why those exist. I tested those for a good hour, trying to get them to work and despairing until I found that /p:ScriptDatabaseOptions=False does the trick.