---
name: sql-database-projects
description: >-
Builds and publishes a SQL database project against Azure SQL Database: the SDK-style
`.sqlproj` on `Microsoft.Build.Sql`, the target platform that decides what the build actually
validates, pre and post deployment scripts, the refactorlog, and code analysis. Use when a user
asks to "create a SQL database project", "build a dacpac", "publish a dacpac to Azure SQL",
"add a post-deployment script", "rename a column without losing its data", or "turn on code
analysis", and reports "the build passed but the publish failed", "the deploy said success and
the data is gone", or "the post-deployment script failed and the table already changed". Covers
what `dotnet build` does not check, what SqlPackage does with a mismatched target platform, and
which publish options change data rather than schema. github-actions-for-sql owns the pipeline,
schema-migrations-safely the change doctrine.
---
# Build and publish a SQL database project
**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
```bash
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:
```xml
Microsoft.Data.Tools.Schema.Sql.Sql170DatabaseSchemaProvider
Microsoft.Data.Tools.Schema.Sql.SqlAzureV12DatabaseSchemaProvider
```
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:
```text
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:
```text
*** An error occurred during deployment plan generation. Deployment cannot continue.
A project which specifies 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:
```bash
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:
```text
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:
```bash
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 |
```xml
```
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 `` 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:
```text
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:
```bash
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:
```xml
```
With it declared, the alert is gone and the operation changed:
```xml