microsoft / microsoft/DacFx

SQLPackage Extract generating unusable .sql with /p:ExtractTarget=File

Open
#655 0 comments 1 reaction 0 assignees View on GitHub

Nobody has claimed this yet.

bug
Dominant language
C#
Stars
460
Forks
29
Avg merge
4d 9h
Merged PRs (30d)
7

Description

- SqlPackage or DacFx Version:
- .NET Framework (Windows-only) or .NET Core:
- Environment (local platform and source/target platforms):

**Steps to Reproduce:**

1. Run SqlPackage extract against a database containing multiple schemas, user-defined types, and objects with dependencies.
Command used: `sqlpackage /Action:Extract /SourceServerName:shared-server-1.database.windows.net /SourceDatabaseName:adv-works-7e9sf /SourceUser:"username" /SourcePassword:"Password" /TargetFile:"test.sql" /p:ExtractAllTableData=False /p:ExtractTarget=File /p:VerifyExtraction=true`
2. Inspect the generated test.sql file.
3. Attempt to execute the generated test.sql file against a new or empty database.

**Expected behavior:**

The test.sql file generated by the extract operation should be topologically sorted. This means all dependency objects should be created before the objects that depend on them. For instance, CREATE SCHEMA and CREATE TYPE statements should appear at the beginning of the file, before any CREATE TABLE statements that use them. The resulting script should be executable and should successfully recreate the database schema.

**Actual behavior:**
The order of SQL commands in the file generated by `SqlPackage /Action:Extract /p:ExtractTarget=File` is not topologically sorted.

An investigation of the generated test.sql file shows that critical dependency statements are located at the end of the file instead of the beginning. Specifically:

`CREATE SCHEMA [rubrik]` and `CREATE SCHEMA [SalesLT]` statements appear at the end of the script.
User-defined types like `[dbo].[NameStyle]` and `[dbo].[Name]` are created after tables that depend on them.
This incorrect ordering makes the script unusable and causes numerous errors upon execution, such as:

The error while trying to use the `.sql` file
![Image](https://github.com/user-attachments/assets/b26d4bf4-25dc-45a7-b7dc-87bc9394725d)
The `.sql` generated

_Made it .txt as github wasn't allowing to attach .sql file_

[test.txt](https://github.com/user-attachments/files/20824876/test.txt)

**Did this occur in prior versions? If not - which version(s) did it work in?**
Yes it did
(DacFx/SqlPackage/SSMS/Azure Data Studio)

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start by reproducing the issue with the supplied SqlPackage Extract command and inspect the generated test.sql and attached test.txt. Verify whether schemas and user-defined types are emitted after dependent tables; done means the extracted script is topologically ordered and executes successfully against a new database.

Written by the indexing model from the issue text.

Assessment

Tech stack
sql
Domain
database
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.