Creating Multiple SQL Server Database Backup Formats in SSDT - Part 1

By: Haroon Ashraf   |   Updated: 2019-06-13   |   Comments   |   Related: 1 | 2 | > Backup

Problem

As a database developer, I would like to create a sample database to be readily available in multiple backup formats for database development testing or research purposes, but how can I make sure that all the different backup types remain consistent.

Solution

The solution is to set up a SQL Database Project as single source of truth and create database objects along with data scripts to be deployed to the target environment(s) and then create the following backup types to be readily available from there:

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

Why Multiple Backup Types?

These multiple backup types are somewhat analogous to Microsoft sample databases available in multiple backup formats available to be used.

The main benefit of multiple database backup types availability of sample database is the number of options for database developers and testers who sometimes just need the setup scripts (to instantly create sample database) and sometimes need DACPAC (in case of working with strictly data-tier application) depending on their requirements and a single sample database readily available in multiple formats is sufficient to address multiple needs.

This tip is about creating SQL Database Project, database objects and deploying them to a target environment to extract multiple backup types initially using SQL Server Data Tools (SSDT) followed by SQL Server Management Studio (SSMS).

Plan Your Database Journey

It is always good to have some plan in mind or better on paper about how to achieve the objective.

In the case of creating multiple backup types for a database we must consider things which give us indication about the feasibility and applicability of the project.

Let's go through some important points which can help us to achieve the objective.

Is It a Sample Database?

If you are intending to create a sample database to be used over time by different types of database professionals for different tasks then keeping multiple database backup types makes a lot of sense.

For example, a database developer would like to create a fresh database from the DACPAC while a database tester may want to create the same database from the setup script.

Internal or External Use

You also have to decide whether it is going to be used internally by your team members or externally by everyone.

If it is for internal purposes only then feel free to work on it, but if it is going to be used externally by anyone then please take extra care since there is no way any sensitive information such as your user name and/or password can be shared publicly by mistake.

Private or Public

Similarly, if your sample database is to be used internally then you have to create a private repository online else public repository is fine.

Choosing Source Control

Choosing a source control system to ensure you preserve your sample database well is vital and you are free to choose any source control which suits your requirements I personally prefer Git source control.

However, Git source control implementation is beyond the scope of this tip since we are going to be entirely focused on the methods to create multiple backup types for a sample database.

Begin the Project

Let's begin the project, but just before that it is good to understand what is expected from the readers of this tip to comfortably follow the steps mentioned in it.

Prerequisites

This tip assumes that the readers are familiar with SQL database scripting along with using SQL Server Data Tools (SSDT) and SQL Server Management Studio (SSMS) to perform day to day database related tasks.

Start from SQL Database Project

Please get started by creating Watches SQL Database Project under Watches Database Project solution in Visual Studio:

Add Tables Structure

Add tables to the project by first creating a folder dbo and then another folder Tables under dbo as shown below:

Create Watch Table

Right click Tables folder and click Add then click Table… to add a new table Watch by writing the following code:

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)
)

Add Reference Data for the table

Add some reference data for the Watch table by creating Reference Data folder under the Watch database project.

Right click Reference Data folder Add a new script called Watch.data.sql as shown below:

-- 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

The project which now contains reference data script is as follows:

Please refer to this tip to get more understanding of how to add data scripts (reference data).

Add Post Deployment Script

A post deployment script which is going to call the data script is required now.

Right click Watches project and click Add followed by clicking Script and then select Post-Deployment Script from the dialogue windows and finally click Add:

After making sure that SQLCMD mode is enabled please write the following code in post deployment script:

/*
Post-Deployment Script Template
 This file contains SQL statements that will be appended to the build script.
 Use SQLCMD syntax to include a file in the post-deployment script.
 Example:      :r .\myfile.sql
 Use SQLCMD syntax to reference a variable in the post-deployment script.
 Example:      :setvar TableName MyTable
               SELECT * FROM [$(TableName)]
--------------------------------------------------------------------------------------
*/
:r ".\Reference Data\Watch.data.sql"

Please go through this tip to learn more about enabling SQLCMD mode.

Setup Target Platform and Debug Database

Right click Watches and click Properties to view Project Properties.

Please set the Target platform as per your requirement, we are choosing SQL Server 2014 in this walkthrough:

Next, referring to project properties again please check under Debug tab debug database is set to be deployed in localdb and please ensure Always re-create database deployment option is checked:

Debug the Project to deploy changes

Press F5 to deploy changes to the debug database and this process, if successful, is going to create a fresh Watches sample database in localdb:

View Watches Sample Database

Go to SQL Server Object Explorer and after making sure you are connected to (localdb)\ProjectsV13 (in our case) expand Databases node to locate newly created sample database Watches:

Your sample database project should look like this now:

Your Watches Database Project is single source of truth for Watches sample database created in SQL Server Data Tools (SSDT), which means based on this project output (Watches database) we can now create multiple backup types for the sample database.

Creating Multiple SQL Server Backup Types

Now that the sample database has been successfully created from the database project using SQL Server Data Tools (SSDT) we can now create different backup types for Watches sample database.

Creating DACPAC Format

The DACPAC format which is traditionally a backup of the structure of the database and when needed it contains structure along with the reference data, is the easiest to create once you have SQL Database Project.

The time you debug your database project if compiled successfully debugging generates a DACPAC file by default in bin\Debug folder of the project.

Since we have successfully debugged the project so please locate the Debug folder under bin directory and check for DACPAC file:

So, DACPAC backup type is already prepared and ready to be used.

Creating Setup Script Format

Creating setup script-based backup is also easy once you have an in-place SQL Database Project.

Start the setup script by creating the database first as follows:

Create Database Watches;
GO

Open the Watch table defined in SQL Database Project and copy its source script from the code window:

Open the Watch data script under Reference Data folder and copy the scripts.

The complete setup script is as follows:

-- Watches database setup script
Create Database Watches;
GO
 
USE Watches
 
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

Save the setup script and that’s it.

Creating BACPAC Format

To create a BACPAC we need to use SQL Server Management Studio (SSMS).

Open SQL Server Management Studio (SSMS) and connect to the SQL Server localdb instance in which the project debug database Watches is deployed.

Select Watches sample database under Databases node.

Right click Watches, click Tasks and then click Export Data-tier Application… option:

Click Next as shown below:

In the next screen of the wizard you will be asked to select location where the BACPAC is saved.

Select the desired location and click Next as follows:

Finally click Finish and see BACPAC getting created:

Check the BACPAC file:

Creating BACKUP Format

The final type of backup is the classic database backup file which is most commonly used to create database backups.

Right click Watches database, click Tasks and then click Back Up… as shown below:

Select the desired location where backup needs to be created and click OK:

The backup is going to be created instantly if there is nothing wrong with it.

Navigate to the backup folder to see it yourself:

Congratulations, multiple database backup types have been successfully created for the Watches database while all these backup types are based on SQL Database Project created and managed in SQL Server Data Tools (SSDT).

Next Steps

• Please try to create multiple backup types for the database mentioned in this tip
• To get better understanding of reference data please refer to this tip and this tip
• Please try arranging your backup types either in the solution folder of the same SQL Database Project or under the same repository for easy access and modification when required.  For example, you can create the following folders at the repository level:
  • Setup Script
  • BACKUP
  • BACPAC
  • SQL Database Project

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