Generating Visual Studio SQL Database Projects from the command line

author Richard Fennell calendar Sep 27, 2024 · 2 min read  ·
Share on: linkedin copy

This is is one of those posts I write so I remember how to do something in the future.

Background

I recently had a need to generate many Visual Studio SQL Database Projects from existing databases. Being a good ’lazy developer’ I wanted to do this from the command line so I could automate the process, but it took me far to long to work out how

The Manual Way

If you only have one database to import you can do this manually by using the Import option in Visual Studio for an individual SQL Database Project.

  1. Open Visual Studio
  2. Create a new project using the SQL Server -> SQL Server Database Project template
  3. Right click on the project in the Solution Explorer and select Import -> Database and follow the wizard.

Import Database

The Command Line Way

For some reason I really struggled to find the command line syntax to perform the same action, so I am documenting it here for future reference.

The key command is SqlPackage.exe and the syntax needed will be something similar to

sqlpackage /Action:Extract /SourceConnectionString:"Server=tcp:{instance},{port};Initial Catalog={databasename};TrustServerCertificate=True;integrated security=true;" /TargetFile:{projectname} /p:ExtractTarget=SchemaObjectType

This command will generate an database project in a folder of the name specified using /TargetFile, where each SQL object has it’s own file containing the appropriate SQL CREATE script.

The ‘magic’ that took me too long to fine was that the /p:ExtractTarget=SchemaObjectType parameter is required. This instructs SQLpackage to generate a project structure and not extract to a single SQL file or DACPAC.

I am not sure whether my internet search powers were weak, or if this usage is just poorly documented, but I hope this post save future me, and others, some time.