This owns the tooling: the project file, the build, the dacpac and the publish. It does not
own how to sequence a schema change (schema-migrations-safely), running the publish from a
pipeline (github-actions-for-sql), or deploying the application beside it
(deploy-app-to-azure). Open sqlpackage-import-export when the job is moving a whole database
rather than building one, or when the artifact is a bacpac.
Verified 2026-09-03 with the .NET SDK 8.0.421 on macOS, Microsoft.Build.Sql 2.2.0,
Microsoft.Build.Sql.Templates 2.2.0 and SqlPackage 170.4.83.3, publishing into the Azure SQL
Database container reporting EngineEdition 5, SQL Azure, 12.0.2000.8.
The build runs anywhere, and SqlPackage is a separate install
dotnet new install Microsoft.Build.Sql.Templates
dotnet new sqlproj -n ShopDb -o ShopDb --target-platform SqlAzureV12
dotnet build ShopDb # writes ShopDb/bin/Debug/ShopDb.dacpac
dotnet tool install -g microsoft.sqlpackage
It is an SDK-style project, so dotnet build is the whole build story on Linux, macOS and Windows
alike, with no IDE and no Windows-only component. SqlPackage is not installed by the SDK, is not
on a hosted build image by default, and every publish below needs it.
The template's default target is not Azure SQL Database
dotnet new sqlproj --help lists ten platforms and defaults to Sql170, the box product. The
choice lands in the project file as one property, and nothing in the build output announces it:
<DSP>Microsoft.Data.Tools.Schema.Sql.Sql170DatabaseSchemaProvider</DSP> <!-- the default -->
<DSP>Microsoft.Data.Tools.Schema.Sql.SqlAzureV12DatabaseSchemaProvider</DSP> <!-- Azure SQL Database -->
A project holding CREATE QUEUE [dbo].[OrderQueue];, which Azure SQL Database has never
supported, built under both:
| Target platform | dotnet build |
Then /Action:Publish to Azure SQL Database |
|---|---|---|
Sql170, the template default |
succeeds, 0 errors | refused before any change, exit 1 |
SqlAzureV12 |
fails, 1 error | never reached |
Under SqlAzureV12 the build names the file and the line:
OrderQueue.sql(1,1,1,1): Build error SQL70015: Statement 'CREATE QUEUE' is not supported for
the targeted platform.
Under the default the objection arrives from SqlPackage instead, during plan generation:
*** An error occurred during deployment plan generation. Deployment cannot continue.
A project which specifies <the project's target platform> as the target platform cannot be
published to Microsoft Azure SQL Database v12.
Nothing half-deploys from this cause: SqlPackage compares the dacpac's declared platform against the server and stops. It still costs a release, because the build went green and the artifact was tagged before anyone learned. Check an inherited project without editing it, by overriding the provider on the command line:
dotnet build ShopDb -t:Rebuild /p:DSP=Microsoft.Data.Tools.Schema.Sql.SqlAzureV12DatabaseSchemaProvider
If that fails where the ordinary build passed, the project is not targeting Azure SQL Database.
/p:AllowIncompatiblePlatform=true on the publish forces past the refusal instead of fixing it.
It defaults to False. Leave it there.
The right target validates statements, not references
SqlAzureV12 does not check that a referenced object exists. A procedure calling a mail or agent
stored procedure Azure SQL Database does not have builds under the Azure target with only:
Build warning SQL71502: Procedure: [dbo].[DoWork] has an unresolved reference to object
[dbo].[NoSuchTable].
Warnings do not fail a build, so the procedure reaches the database and fails the first time it runs. Answer each one, or escalate the whole class:
dotnet build ShopDb -t:Rebuild -p:TreatTSqlWarningsAsErrors=true
Measured: every SQL71502 becomes Build error SQL71502 and the build fails. Where one warning
is genuinely fine, SuppressTSqlWarnings takes a comma-separated list of numbers.
Deployment scripts ride in the dacpac and are read by nothing
The SDK globs **/*.sql into the model, then removes whatever is declared as a deployment script
or as None. There is a RefactorLog item type and no glob creates one.
| Item type | Globbed | Parsed and validated |
|---|---|---|
Build, ordinary object files |
yes, **/*.sql |
yes |
PreDeploy, PostDeploy |
no, declare them | no, not at all |
RefactorLog |
no, declare it | not applicable |
<ItemGroup>
<PreDeploy Include="Script.PreDeployment.sql" />
<PostDeploy Include="Script.PostDeployment.sql" />
<RefactorLog Include="ShopDb.refactorlog" />
</ItemGroup>
A post-deployment script whose entire body is INSERT INTO dbo.NoSuchTable (id) VALUES (1);
builds with 0 errors and 0 warnings. The same statement inside a stored procedure produces
SQL71502. A project takes one pre-deployment and one post-deployment script; chain more with
:r .\scripts\script1.sql inside them, and add <Build Remove="scripts\script1.sql" /> so the
chained files stay out of the model. Seed data belongs here, written so a second run is a no-op,
never in a Build file.
A failed publish does not roll back the schema
Publishing a project that adds a column and carries a post-deployment script that fails:
Altering Table [dbo].[Product]...
Updating database (Failed)
Error SQL72014: ... Msg 208, Level 16, State 1, Line 2 Invalid object name 'dbo.NoSuchTable'.
Error SQL72045: Script execution error.
SqlPackage exits 1, and the new column is there and stays there.
/p:IncludeTransactionalScripts=True changes nothing here: deployment scripts run outside the
transactional block, which is exactly the part that failed. So a red pipeline step is not a
database that stayed still. Read the schema after a failed publish before deciding anything.
schema-migrations-safely owns why idempotent scripts are a rule rather than a nicety.
The rename, which is the one that costs data
To a schema comparison a column rename is a drop and an add. The refactorlog turns it back into a rename, and it is the easiest thing here to leave half-wired, because the file existing is not the file being used.
Measured on a Product table holding two rows:
| What the project has | Publish result |
|---|---|
| the rename, no refactorlog | blocked: Msg 50000 ... Rows were detected. The schema update is terminating because data loss might occur. exit 1 |
| the file on disk but not declared | the same block. The build succeeded and said nothing |
declared as a RefactorLog item |
Rename [dbo].[Product].[Name] to ProductName, both rows keep their values, exit 0 |
no refactorlog, /p:BlockOnPossibleDataLoss=False |
Successfully published database, exit 0, both values now null |
The last row is the whole reason this skill exists: turning that option off to get a pipeline green converts a blocked deployment into a successful one that dropped a column.
You do not need a database to find out which row you are in. DeployReport reads a dacpac against another dacpac, so the plan is readable before any server is involved:
sqlpackage /Action:DeployReport /SourceFile:After/bin/Debug/After.dacpac \
/TargetFile:Before/bin/Debug/Before.dacpac /TargetDatabaseName:ShopDb /OutputPath:plan.xml
Measured 2026-09-03, the same rename twice. Without the refactorlog declared:
<Alerts><Alert Name="DataIssue"><Issue Value="The column [dbo].[Product].[Name] is being dropped,
data loss could occur." Id="1" /></Alert></Alerts><Operations><Operation Name="Alter">
With it declared, the alert is gone and the operation changed:
<Alerts /><Operations><Operation Name="Rename"><Item Value="[dbo].[Product].[ProductName]"
Type="SqlSimpleColumn" /></Operation></Operations>
The first publish carrying a refactorlog creates dbo.__RefactorLog in the target and records the
operation key, so each rename applies once. That table is deployment state: copying a database
without it makes an already-applied rename look outstanding.
The database is not the project
/p:DropObjectsNotInSource defaults to False. Measured: a table and a procedure created by
hand in the target both survive a publish of a project that contains neither, and the publish
reports success. Turning it on drops them and keeps __RefactorLog:
Dropping Table [dbo].[Legacy]...
Dropping Procedure [dbo].[LegacyProc]...
Neither setting is the safe one. Decide whether this database is a mirror of the project or a superset holding things the project does not describe, and never turn the option on without reading a plan first.
Code analysis is on request, and advisory until escalated
<RunSqlCodeAnalysis>true</RunSqlCodeAnalysis>
Measured on a procedure using SELECT * and @@IDENTITY: StaticCodeAnalysis warning SR0001,
SR0008, and Build succeeded. 0 Error(s), 2 Warning(s). Warnings in a log nobody reads are not
a gate. Escalate one rule with +!, which fails the build:
dotnet build ShopDb -t:Rebuild -p:SqlCodeAnalysisRules='+!Microsoft.Rules.Data.SR0001'
Measured: StaticCodeAnalysis error SR0001, 1 error and 1 warning, Build FAILED. Two traps. The
sqlcodeanalysis template does not turn analysis on: it scaffolds a C# project for writing a
custom rule, which then has to be packaged and referenced. And dotnet build skips analysis
entirely on a project that is up to date, so a rerun reporting zero warnings may have analysed
nothing. Use -t:Rebuild whenever the warning count is the answer you want.
Publishing
sqlpackage /Action:Publish /SourceFile:ShopDb/bin/Debug/ShopDb.dacpac \
/TargetConnectionString:"Server=tcp:<server-name>.database.windows.net,1433;Initial Catalog=<database>;Authentication=Active Directory Default;Encrypt=True;"
Read the plan against the real target before applying it. Both of these write a file and change nothing:
sqlpackage /Action:DeployReport /SourceFile:ShopDb/bin/Debug/ShopDb.dacpac \
/TargetConnectionString:"Server=tcp:<server-name>.database.windows.net,1433;Initial Catalog=<database>;Authentication=Active Directory Default;Encrypt=True;" \
/OutputPath:plan.xml
sqlpackage /Action:Script /SourceFile:ShopDb/bin/Debug/ShopDb.dacpac \
/TargetConnectionString:"Server=tcp:<server-name>.database.windows.net,1433;Initial Catalog=<database>;Authentication=Active Directory Default;Encrypt=True;" \
/DeployScriptPath:publish.sql
/OutputPath is the DeployReport parameter and DeployReport does not accept /DeployReportPath;
github-actions-for-sql owns that trap and the pipeline around it.
/Action:Publish does not create the logical server, and in Azure that has to exist first.
It does create the database. Measured against the container reporting EngineEdition 5:
publishing a dacpac at a database name that did not exist printed Creating database <name>...,
then created the table, and the row count in sys.databases went from 0 to 1. Do not add a create
step before a publish on the assumption that it is required.
Check it worked
Three checks in the order the failures happen. The first two need no database at all.
The dacpac declares the platform the publish will compare against the server, and that is the value that decides the refusal, not the text in the project file:
unzip -p ShopDb/bin/Debug/ShopDb.dacpac model.xml | grep -o 'DspName="[^"]*"'
Expect exactly DspName="Microsoft.Data.Tools.Schema.Sql.SqlAzureV12DatabaseSchemaProvider".
Anything else and the publish to Azure SQL Database is refused during plan generation.
Everything the build refuses to read sits in the package under a fixed name:
unzip -l ShopDb/bin/Debug/ShopDb.dacpac
Expect refactor.xml if this release carries a rename, postdeploy.sql if it carries a
post-deployment script, and predeploy.sql if it carries a pre-deployment one. Measured
2026-09-03: undeclared, the dacpac holds four entries and no refactor.xml; declared, five. A
missing entry means the item was never declared, and the build did not complain.
Then, after the publish, ask the target whether anything is left:
sqlpackage /Action:DeployReport /SourceFile:ShopDb/bin/Debug/ShopDb.dacpac \
/TargetConnectionString:"<the same connection string>" /OutputPath:after.xml
grep -c "<Operation " after.xml
Expect 0, and expect <Alerts /> with nothing inside it. A publish that landed leaves the next
deploy report nothing to do, so any count above zero names an object the run did not apply.
References
- Open target-platform-and-refactorlog-runs.md when a table above needs reproducing, when a build or publish message you are seeing does not match one quoted here, or to look up a property or SqlPackage option this body only names in passing.
sdk/Sdk.propsandsdk/Sdk.targetsinside theMicrosoft.Build.Sqlpackage in the local NuGet cache: read them when a file is sitting in the project directory and is not in the build.- Microsoft Learn is the authority on whether a property or item type exists. Open SQL projects properties, target platform or refactoring before writing a property this body does not name.