Tutorial: Store the database schema in Git

• .NET 8 SDK
• Visual Studio 2022 Community, Professional, or Enterprise
• Install SQL Server Data Tools (SSDT) for Visual Studio

• .NET 8 SDK
• Visual Studio Code
• SQL Database Projects extension

• .NET SDK
• SQL Server Management Studio (SSMS)
• Database DevOps workload installed in SSMS

• .NET 8 SDK
• SqlPackage CLI
• Microsoft.Build.Sql.Templates .NET templates

# install SqlPackage CLI
dotnet tool install -g Microsoft.SqlPackage

# install Microsoft.Build.Sql.Templates
dotnet new install Microsoft.Build.Sql.Templates

From the SQL Server Object Explorer in Visual Studio, right-click the database you want to create a project from and select Create New Project....

In the Create New Project dialog, enter a project name. The project name doesn't need to match a database name. Verify and modify the project location as needed. The default import settings import the objects into folders by schema, then object type. You can modify the import settings to change the folder structure or to include permissions in the objects being imported. Start the import.

The Import Database dialog displays the import progress as messages. When the import finishes, you can see the imported objects in the Solution Explorer. The process stores the logs in a file in the project directory under Import Schema Logs. Select Finish.

In the database object explorer view in Visual Studio Code, select a database you want to create a project from. Right-click the database and select Create Project from Database.

In Visual Studio Code, the Create project from database dialog requires the project name and location. The default import settings import the objects into folders by schema, then object type. You can select a different folder structure or choose to include permissions in the objects being imported before selecting Create.

Open the Database Projects view to see the new project and imported object definitions.

From the Object Explorer in SQL Server Management Studio, right-click the database you want to create a project from and select Create Project from Database... under the Tasks menu.

In the Create SQL Project from Database dialog, verify the selected database and modify the project name and location as needed. The new project is created with the objects from the selected database and arranged in folders by schema, then object type.

A solution file is automatically created in the folder above the project folder, and opens in the Solution Explorer after creation. If you want to add your new project to an existing solution, select Add to solution and browse to the solution file. Select Create to create the project.

Use the SqlPackage CLI to extract the schema of an existing database to a SQL project. The following SqlPackage command extracts the schema of a database to a complete SQL project. The .sql files are organized by nested schema and object type folders and a .sqlproj file is created to manage the project with properties set matching the database's options.

sqlpackage /a:Extract /ssn:localhost /sdn:MyDatabase /tf:MyDatabaseProject /p:ExtractTarget=SqlProject

With the SQL project open in Visual Studio, create a local Git repository and push it to a remote.

1. Select Git > Create Git Repository from the menu bar.

2. In the Create a Git Repository dialog, under the Push to a new remote section, choose either GitHub or Azure DevOps. If you choose to push to GitHub, sign in to your GitHub account when prompted. If you choose Azure DevOps, sign in to your Azure DevOps account and select or create a project.

3. Enter a repository name and description. Ensure the .gitignore template is set to VisualStudio and select Create and Push.

Visual Studio creates the local Git repository, makes an initial commit with your SQL project files, and pushes it to the remote.

To verify the code was pushed successfully, open a browser and navigate to your repository on GitHub (https://github.com) or Azure DevOps (https://dev.azure.com). You should see the SQL project files, including the .sqlproj file and the .sql object definitions.

For more information, see About Git in Visual Studio.

With the SQL project folder open in Visual Studio Code, initialize a local Git repository and push it to a remote.

1. Open the Source Control view by selecting the Source Control icon in the Activity Bar, or press Ctrl+Shift+G.

2. Select Initialize Repository to create a new Git repository in the current folder.

3. All project files appear as untracked changes. Select the + icon next to Changes to stage all files.

4. Enter a commit message such as Initial commit of SQL project in the message input box and select the Commit button (checkmark icon).

5. Select Publish Branch in the Source Control view to push your local repository to a remote. Choose whether to publish to GitHub or Azure DevOps and follow the sign-in prompts. Select a repository visibility (public or private) and confirm.

To verify the code was pushed successfully, open a browser and navigate to your repository on GitHub (https://github.com) or Azure DevOps (https://dev.azure.com). You should see the SQL project files, including the .sqlproj file and the .sql object definitions.

For more information, see Source Control in Visual Studio Code.

With the SQL project open in SQL Server Management Studio, create a local Git repository and push it to a remote.

1. Select Git > Create Git Repository from the menu bar.

2. In the Create a Git Repository dialog, under the Push to a new remote section, choose either GitHub or Azure DevOps. If you choose to push to GitHub, sign in to your GitHub account when prompted. If you choose Azure DevOps, sign in to your Azure DevOps account and select or create a project.

3. Enter a repository name and description. Ensure the .gitignore template is set to VisualStudio and select Create and Push.

SSMS creates the local Git repository, makes an initial commit with your SQL project files, and pushes it to the remote.

To verify the code was pushed successfully, open a browser and navigate to your repository on GitHub (https://github.com) or Azure DevOps (https://dev.azure.com). You should see the SQL project files, including the .sqlproj file and the .sql object definitions.

For more information, see About Git in Visual Studio.

From the SQL project directory, initialize a local Git repository, commit all files, and push to a remote.

1. Initialize a new Git repository, add a .gitignore file, and make an initial commit:

   cd MyDatabaseProject
   git init
   dotnet new gitignore
   git add .
   git commit -m "Initial commit of SQL project"
   

2. Create a new remote repository in GitHub or Azure DevOps:

   • GitHub: Go to https://github.com/new and create a new repository. Don't initialize it with a README, .gitignore, or license.
   • Azure DevOps: Go to your Azure DevOps project, select Repos, and create a new repository. Uncheck Add a README.

3. Add the remote and push your initial commit:

   git remote add origin <your-repository-url>
   git push -u origin main
   

   Replace <your-repository-url> with the URL provided by GitHub or Azure DevOps when you created the repository.

To verify the code is pushed successfully, open a browser and go to your repository. You should see the SQL project files, including the .sqlproj file and the .sql object definitions.

Use schema compare to update the SQL project from the database.

1. In SQL Server Object Explorer, right-click the database and select Schema Compare.

2. In the Schema Compare window, the database is set as the source. Select the Select Target dropdown list and choose Select Target.

3. In the Select Target Schema dialog, select Project and choose your SQL database project. Select OK.

4. Select Compare to run the comparison. The results display the differences between the database (source) and the project (target).

5. Review the differences. Uncheck any changes you don't want to apply to the project.

6. Select Update to apply the selected changes to the SQL project.

The project files are updated to match the database schema. Review the changes in the Git Changes window, enter a commit message, and select Commit All. Select Push to push the changes to the remote repository.

Use the Update Project from Database command to update the SQL project from the database.

1. In the database object explorer, right-click the database and select SQL Database Projects > Update Project from Database.

2. Select the SQL database project to update and confirm.

3. The comparison runs and applies the changes from the database to the project. The updated .sql files appear in the Source Control view as modified files.

Review the changes in the Source Control view, stage the modified files, enter a commit message, and select Commit. Select Sync Changes to push the commit to the remote repository.

Use schema compare to update the SQL project from the database.

1. In Object Explorer, right-click the database and select Tasks > Schema Compare (preview).

2. In the Schema Compare window, the database is set as the source. Select the target ellipsis button and choose your SQL database project as the target.

3. Select Compare to run the comparison. The results display the differences between the database (source) and the project (target).

4. Review the differences. Uncheck any changes you don't want to apply to the project.

5. Select Apply to apply the selected changes to the SQL project.

The project files are updated to match the database schema. Review the changes in the Git Changes window, enter a commit message, and select Commit All. Select Push to push the changes to the remote repository.

Use SqlPackage to regenerate the SQL project from the database, replacing the existing project files with the current database schema. While the other SQL project tools provide a graphical interface, SqlPackage offers a command-line approach for automation that doesn't provide an opportunity to review the file changes before applying them.

1. Remove the existing SQL object files and regenerate the project:

   rm -rf MyDatabaseProject
   sqlpackage /a:Extract /ssn:localhost /sdn:MyDatabase /tf:MyDatabaseProject /p:ExtractTarget=SqlProject
   

2. Review the changes with Git and commit:

   cd MyDatabaseProject
   dotnet new gitignore
   git add .
   git status
   git commit -m "Update project from database changes"
   git push
   

1. Select Git > Pull from the menu bar to pull the latest changes from the remote repository.

2. In Solution Explorer, right-click the SQL database project node and select Schema Compare.

3. In the Schema Compare window, the project is set as the source. Select the Select Target dropdown list and choose Select Target.

4. In the Select Target Schema dialog, select Database and choose your local development database connection. Select OK.

5. Select Compare to run the comparison. The results display the differences between the project (source) and the database (target).

6. Review the differences to verify the changes are expected.

7. Select Update to apply the changes to the database.

1. Open the Source Control view and select Pull from the ... menu (or use the sync button in the Status Bar) to pull the latest changes from the remote repository.

2. In the Database Projects view, right-click the SQL database project and select Schema Compare.

3. In the Schema Compare window, the project is set as the source. Select the target ellipsis button and choose your local development database connection as the target.

4. Select Compare to run the comparison. The results display the differences between the project (source) and the database (target).

5. Review the differences to verify the changes are expected.

6. Select Apply to apply the changes to the database.

1. Open a terminal and run git pull in the project directory to pull the latest changes from the remote repository.

2. In Solution Explorer, right-click the SQL database project node and select Schema Compare (preview).

3. In the Schema Compare window, the project is set as the source. Select the target and choose your local development database connection.

4. Select Compare to run the comparison. The results display the differences between the project (source) and the database (target).

5. Review the differences to verify the changes are expected.

6. Select Apply to apply the changes to the database.

1. Pull the latest changes from the remote repository:

   cd MyDatabaseProject
   git pull
   

2. Build the project to verify the changes compile successfully:

   dotnet build
   

3. Deploy the changes to your local development database using SqlPackage:

   sqlpackage /a:Publish /sf:bin/Debug/MyDatabaseProject.dacpac /tsn:localhost /tdn:MyDatabase
   

   Replace MyDatabaseProject.dacpac with the name of your .dacpac output file, and update the target server (/tsn) and database (/tdn) values as needed.