Manage Multiple Database Backup Formats in SQL Server Data Tools (SSDT) - Part 2

By: Haroon Ashraf   |   Updated: 2019-07-30   |   Comments   |   Related: 1 | 2 | > Backup

Problem

As a SQL Server database developer, I would like to modify a sample database available in multiple backup formats in such a way that the changes made are reflected in all the backup formats and the sample database remains consistent.

Solution

The solution is to apply the desired changes to the SQL Database Project which must be a single source of truth and then create the different backup formats based on the most recent version of the SQL Database Project:

1. DACPAC
2. Setup Script
3. BACPAC
4. Backup

Why SQL Database Project is Single Source of Truth

You might be wondering why a SQL Database Project is single source of truth for the database while the rest of the backup formats follow it.

This is one of the core purposes of SQL Database Project when it was designed.  That is to offer declarative database development in which you only declare what you want and then you deploy those changes to a debug database.  Later on, you can use publish scripts (manually or automatically) to deploy the changes to other environments such as shared dev, test, QA and Production.

In this way the databases in different environments remain in sync through the SQL Database Project which can be rightly called the single source of truth (for the database).

This is illustrated as follows:

Pre-Requisites

Let us first go through the pre-requisites of the tip to follow the concepts and their implementation (walkthrough) in the best possible manner.

SQL Database Project and T-SQL Concepts

This tip assumes that the readers are familiar with SQL Server Data Tools (SSDT) particularly SQL Database Project along with day to day database scripting tasks using T-SQL.

For more information about how to use SQL Database Project please refer to the tip Developing Similar Structured Multi-Customer Databases with SQL Server Data Tools (SSDT)

Watches SQL Database Project

Please remember that this tip also assumes that the readers who are interested to implement the walkthrough have already implemented the steps mentioned in the previous tip Creating Multiple SQL Server Database Backup Formats in SSDT - Part 1.

Please check the list below to ensure that your SQL Database Project has the following things:

1. A Watch Table
2. Reference data for the Watch table
3. Post Deployment Script to populate Watch table
4. The debug database name is Watch database

Multiple Backups Created by Database Project

One of the prerequisites of this tip is creating the following backup types from the SQL Database Project:

1. DACPAC database backup format
2. Setup script database backup format
3. BACPAC database backup format
4. Backup database format

If you have not created these backup types by now and would like to complete these steps then please refer to the first part of this tip Creating Multiple SQL Server Database Backup Formats in SSDT - Part 1.

Changing the Sample Database (Watches)

If we need to make any changes to the sample database, we need to make those changes to the existing SQL Database Project behind the sample database Watches and then we have to ensure those changes are reflected across all the backup formats available for this sample database.

SQL Database Project behind sample database (Watches)

Let us now have a look at the SQL Database Project for the sample database (created in the previous tip):

Adding Watch Order Table (WatchOrder)

We are adding new table to SQL Database Project called WatchOrder.

Right click Tables and click Add then click Table… and then create a new table named WatchOrder based on the following T-SQL script:

CREATE TABLE [dbo].[WatchOrder]
(
   [WatchOrderId] INT NOT NULL IDENTITY(1,1),
   [WatchId] INT NOT NULL,
   [Quantity] INT NOT NULL,
   [OrderDate] DATETIME2 NOT NULL,
   [Price] DECIMAL(10,2) NOT NULL, 
    CONSTRAINT [PK_WatchOrder] PRIMARY KEY ([WatchOrderId]), 
    CONSTRAINT [FK_WatchOrder_ToTable] FOREIGN KEY (WatchId) REFERENCES [Watch]([WatchId]),
)			

Adding Reference Data for Watch Order Table

In the next step, we are adding reference data for the WatchOrder table.

Please add new script under Reference Datafolder and write the following code to populate WatchOrder table:

SET IDENTITY_INSERT [dbo].[WatchOrder] ON
INSERT INTO [dbo].[WatchOrder] ([WatchOrderId], [WatchId], [Quantity], [OrderDate], [Price]) VALUES (1, 1, 1, N'2019-01-10 00:00:00', CAST(150.00 AS Decimal(10, 2)))
INSERT INTO [dbo].[WatchOrder] ([WatchOrderId], [WatchId], [Quantity], [OrderDate], [Price]) VALUES (3, 1, 1, N'2019-01-20 00:00:00', CAST(150.00 AS Decimal(10, 2)))
INSERT INTO [dbo].[WatchOrder] ([WatchOrderId], [WatchId], [Quantity], [OrderDate], [Price]) VALUES (4, 2, 1, N'2019-02-03 00:00:00', CAST(200.00 AS Decimal(10, 2)))
INSERT INTO [dbo].[WatchOrder] ([WatchOrderId], [WatchId], [Quantity], [OrderDate], [Price]) VALUES (5, 2, 2, N'2019-02-15 00:00:00', CAST(400.00 AS Decimal(10, 2)))
INSERT INTO [dbo].[WatchOrder] ([WatchOrderId], [WatchId], [Quantity], [OrderDate], [Price]) VALUES (6, 4, 2, N'2019-03-01 00:00:00', CAST(300.00 AS Decimal(10, 2)))
SET IDENTITY_INSERT [dbo].[WatchOrder] OFF
			

Please refer to the following tips to see detailed steps of creating reference data in SQL Database Project:

1. SQL Server Reference Data Best Practices - Part 1
2. SQL Server Reference Data Best Practices - Part 2

After adding new the WatchOrder table and its reference data the project should look as follows:

Refreshing Multiple Backup Types

Now that changes have been made to the SQL Database Project this is the time to refresh the available backup types starting from the DACPAC.

Tip: Build SQL Database Project Successfully

Please remember that you have to either select Always re-create database deployment option under Debug menu of Project Properties or use Merge script in your reference data to make sure that each time the project is built the post-deployment scripts when populating the tables don’t fail because of data already being present.

Please refer to the following tip if you would like to see all the steps involved in using Always re-create database deployment option:

• Adding Reference Data in Database Continuous Integration (CI) Pipeline

Please refer to following if you are interested to use Merge script option:

• SQL Server Reference Data Best Practices - Part 2

Updating DACPAC Format

Updating DACPAC format is the easiest to do and most preferable choice of deploying your database changes.

As soon as you make changes to your SQL Database Project and press F5 to debug the project a DACPAC is created based on the latest changes after the build is successful.

The debugging generates a DACPAC file by default in bin\Debug folder of the project.

So to update your database DACPAC backup format please press F5 to and then locate DACPAC under bin/debug folder of your project.

The DACPAC can be easily found in the big/debug folder of your SQL Database Project:

Updating Setup Script Format

Updating setup script based on most recent changes to your SQL Database Project is also not a big deal once you have a folder where you are storing your setup script.

Please open your existing setup script and update it as follows:

1. The setup script should begin with Create Database command followed by GO
2. The setup script must contain the scripts to create database objects such as tables
3. The setup script must contain the scripts to populate database tables if there is any reference data

As discussed in the previous part of the tip that the scripts required to create tables are same as the scripts contained in table objects and the scripts to populate database tables are the same as the reference data scripts in the SQL Database Project so copying pasting those scripts onto your setup script works.

Copying all the objects and their reference data scripts the setup script is as follows:

-- Watches database setup script
Create Database Watches;
GO
 
USE Watches
 
-- Creating Watch table
CREATE TABLE [dbo].[Watch]
(
   [WatchId] INT NOT NULL IDENTITY(1,1), 
   [WatchType] VARCHAR(40),
   [Brand] VARCHAR(40),
   [Colour] VARCHAR(20),
   [Price] DECIMAL(10,2)
    CONSTRAINT [PK_WatchType] PRIMARY KEY (WatchId),
);
  
-- Populating Watch table
SET IDENTITY_INSERT dbo.Watch ON
INSERT INTO [dbo].[Watch] ([WatchId], [WatchType], [Brand], [Colour], [Price]) VALUES (1, N'Digital', N'Casio', N'Black', CAST(150.00 AS Decimal(10, 2)))
INSERT INTO [dbo].[Watch] ([WatchId], [WatchType], [Brand], [Colour], [Price]) VALUES (2, N'Analogue', N'Casio', N'Blue', CAST(200.00 AS Decimal(10, 2)))
INSERT INTO [dbo].[Watch] ([WatchId], [WatchType], [Brand], [Colour], [Price]) VALUES (3, N'Smart', N'Polar', N'Yellow', CAST(300.00 AS Decimal(10, 2)))
INSERT INTO [dbo].[Watch] ([WatchId], [WatchType], [Brand], [Colour], [Price]) VALUES (4, N'Sports', N'Timex', N'Black', CAST(150.00 AS Decimal(10, 2)))
INSERT INTO [dbo].[Watch] ([WatchId], [WatchType], [Brand], [Colour], [Price]) VALUES (5, N'Digital', N'Seiko', N'Brown', CAST(185.00 AS Decimal(10, 2)))
SET IDENTITY_INSERT dbo.Watch OFF
 
-- Creating WatchOrder table
CREATE TABLE [dbo].[WatchOrder]
(
   [WatchOrderId] INT NOT NULL IDENTITY(1,1),
   [WatchId] INT NOT NULL,
   [Quantity] INT NOT NULL,
   [OrderDate] DATETIME2 NOT NULL,
   [Price] DECIMAL(10,2) NOT NULL, 
    CONSTRAINT [PK_WatchOrder] PRIMARY KEY ([WatchOrderId]), 
    CONSTRAINT [FK_WatchOrder_ToTable] FOREIGN KEY (WatchId) REFERENCES [Watch]([WatchId]),
)
 
-- Populating WatchOrder table
SET IDENTITY_INSERT [dbo].[WatchOrder] ON
INSERT INTO [dbo].[WatchOrder] ([WatchOrderId], [WatchId], [Quantity], [OrderDate], [Price]) VALUES (1, 1, 1, N'2019-01-10 00:00:00', CAST(150.00 AS Decimal(10, 2)))
INSERT INTO [dbo].[WatchOrder] ([WatchOrderId], [WatchId], [Quantity], [OrderDate], [Price]) VALUES (3, 1, 1, N'2019-01-20 00:00:00', CAST(150.00 AS Decimal(10, 2)))
INSERT INTO [dbo].[WatchOrder] ([WatchOrderId], [WatchId], [Quantity], [OrderDate], [Price]) VALUES (4, 2, 1, N'2019-02-03 00:00:00', CAST(200.00 AS Decimal(10, 2)))
INSERT INTO [dbo].[WatchOrder] ([WatchOrderId], [WatchId], [Quantity], [OrderDate], [Price]) VALUES (5, 2, 2, N'2019-02-15 00:00:00', CAST(400.00 AS Decimal(10, 2)))
INSERT INTO [dbo].[WatchOrder] ([WatchOrderId], [WatchId], [Quantity], [OrderDate], [Price]) VALUES (6, 4, 2, N'2019-03-01 00:00:00', CAST(300.00 AS Decimal(10, 2)))
SET IDENTITY_INSERT [dbo].[WatchOrder] OFF
			

Once you overwrite the existing setup script with the above script you have updated the setup script successfully.

Updating BACPAC Format

Update a BACPAC format based on latest changes made to SQL Database Project in SQL Server Management Studio (SSMS).

Please start SQL Server Management Studio (SSMS) and locate your debug database after connecting to the same SQL instance you used to setup your debug database in the SQL Database Project.

Create a BACPAC database backup by right clicking Watches database, then click Tasks and then click Export Data-tier Application… option and follow steps of the wizard.

Please refer to the previous part of the tip for detailed steps.

Check the BACPAC file:

Creating BACKUP Format

Once you have made the changes to the SQL Database Project and those changes have been deployed to the debug database then creating a backup of the database updates the existing backup of the database, but you have to overwrite the existing database backup.

Please remember that we are not maintaining the historical backups or versions of the sample database for disaster recovery rather we are keeping all the backup types consistent with SQL Database Project that’s we have replaced the existing backup with the new one.

Next, check the backup:

Congratulations, multiple database backup types have been successfully updated through SQL Database Project and in this way the changes made to the SQL Database Project have been reflected across all the database backup types for the sample database Watches.

Next Steps

• Please check how consistent the backup types by doing the following tasks:
  • Creating a test database Watches2 from DACPAC to see if it is same as Watches database
  • Creating a test database Watches3 from BACPAC and compare it with sample database Watches database
  • Restore Backup of the database as Watches4 and see if it matches with Watches database
  • Run the setup script by replacing Watches with Watches5 to create sample database Watches5
• Please try to create and modify through SQL Database Project multiple backup types for the database mentioned in this tip
• Please try to automate the tasks of managing multiple backups

About the author

Haroon Ashraf's interests are Database-Centric Architectures and his expertise includes development, testing, implementation and migration along with Database Life Cycle Management (DLM).

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips