Prashanth Jayaram

SqlPackage.exe – Automate SQL Server Database Restoration using bacpac with PowerShell or Batch techniques

May 7, 2018 by

Data is the key to your organization’s future, but if it’s outdated, irrelevant, or hidden then it’s no good. Maintenance and administration of databases takes a lot of work. As database administrators, we often tend to automate most of these repetitive tasks. A database refresh is one of the most common tasks performed by most of the administrators as part of their daily routine.

Today, database refreshes are quite frequent because of Continuous Integration (CI) and Continuous Deployment (CD). In most of the cases, testing requires a separate but current production dataset to ensure the validity of the desired result.

In today’s world, we rely more on third party tools to perform a Backup and Restore of databases. With many advanced tools and techniques, this is a pretty straight forward approach. Think of the real-world scenarios where customers rely on the native SQL Tools and techniques. Creating an automated database refresh tasks regularly will have a huge impact on the quality of the release management cycles and would save a lot of time for the database administrators.

There are many ways to automate this, some of which are:

  • PowerShell
  • SqlPackage

In this article, we about the following:

  • Details of Sqlcmd
  • The use of the cross-platform tool, Sqlpackage
  • Automation using Windows batch scripting
  • And more…

Using sqlcmd provides flexible ways to execute T-SQLs and SQL script files. As its available on Linux, Windows and Mac, this command line utility plays a vital role in managing the database restore operations in a DevOps pipeline.

PowerShell script to automatically create a bacpac file and restore the database using the created bacpac, using SqlPackage.exe

This section deals with the preparation of a PowerShell script to automate database restoration using the SqlPackage tool which is part of the SQL Server Data Tools suite.

The first step is to prepare and set the environment variables. The SqlPackage tool is installed under C:\Program Files (x86)\Microsoft SQL Server\140\DAC\bin. The script uses sqlcmd and SqlPackage tool; make sure that the path variable is updated accordingly.

  1. The input parameter section lists the source, target SQL databases instance and folder for extracting the bacpac file
  2. Create the bacpac file using export action type
  3. Select the latest bacpac file for further database restoration action
  4. Drop the destination database using the sqlcmd command
  5. Restore he database using import action type.

First, let’s work on the getting and setting the environment variables. Pull the details of the environment variables into a local variable. The output shows if the environment variable contains the path or not (if not, the path is added to the PATH variable).

Defining the required input parameters

  • Backup directory
  • Source database name
  • Source server name
  • Target server name (in this case it’s a Linux machine)

Prepare the target filename

In most of the case, defining a backup file is a standard practice as one backup file may not be enough to safeguard the entire system. So, a backup file name should be more meaningful with timestamps attached to it, and it should speak a little about the data. It includes the following;

  • Filename
  • Extension
  • Timestamp yyyyMMddhhmmss format

Run the SqlPackage tool

Now, run SqlPackage.exe to export the ApexSQLBackup database. This step will create the BACPAC file for an existing database. The following sqlpackage.exe commands are used to export the database. These commands are compatible with all the databases on a local instance or remote instance of SQL Server, or even an Azure SQL Databases.

sqlpackage.exe /a:Export /ssn:<SourceServerName> /sdn:<SourceDatabaseName> /su:<sourceUsername> /sp:<SourcePassword> /tf:<targetFileName>

  • /Action – /a

    specify the action to be performed. In this case it’s Export, used to export the database into a bacpac file

  • /SourceServerName -/ssn

    defines the name of the server hosting the database. It can be an Azure database URI or instance name or named instance

  • /SourceDatabaseName (sdn)

    defines the name of the existing source database

  • /SourceUser (su)

    defines the SQL Server login username

  • /SourcePassword (sp)

    defines the password; In this case, we’re exporting from a trusted SQL instance on Windows. For trusted connections, ignore the /su and /sp parameters

In the following example, the short form is used

Now, we’ll see how to get the latest bacpac file. In this case, selecting an item is done using Get-Childitem cmdlet. The specific files are listed using pattern matching. The output is then piped to get a sorted list in a descending order and then selected the first object of the list into the resultset.

There are some caveats to keep in mind while importing the data. The database must either not exist or be completely empty. The following SQL is used to check for the database existence on the target SQL instance. On its existence, the database is dropped using sqlcmd.

Import the ApexSQLBackup database

To import the database, you can run the following command. These commands work for databases on a local instance or remote instance of SQL Server or even an Azure SQL Database instance:

sqlpackage.exe /a:Import /sf:<SourcefileName> /tsn:<TargetServerName> /tdn:<targetdatabaseName> /su:<sourceUsername> /sp:<SourcePassword>

In this case, we are connecting to a Linux instance.

Listed below are the arguments we’re using for importing the bacpac file. In my example above, I’m using the short form, you can use either long or short forms that suit your requirements or understanding

  • /Action – /a

    the action type, in this case its import, Importing database into Linux SQL instance

  • /SourceFileName -/sf

    the path of the latest bacpac file of the database.

  • /TargetDatabaseName -/sdn

    the name of the target database

  • /SourceUser (su)

    login user name

  • /SourcePassword (sp)

    the password; In this case its uses SQL authentication to connect to SQL on Linux. The /su and /sp parameters are in used in this place.


Copy and save the Appendix A content into sqlpackage.ps1 file

Automated Windows Batch script to create bacpac file and restoring the database using the created bacpac using SqlPackage.exe

A batch file is a collection of commands or executables, run within the command shell (CMD). It’s similar to a bash script in Unix/Linux.

The walkthrough of the batch file is pretty straight forward and it is no different from the PowerShell script that we just saw. I will discuss only the required pieces of the batch file in the next section.

Let’s save the below following script code as a batch file. The filename has an extension of .bat or .cmd.

I would like to take you through the for loop of the code. For example,

FOR /F [“options”] %%parameter IN (‘command to process’) DO command. Here are the steps we’d follow:

  1. Take a dataset
  2. Make a FOR Parameter %%G equal to some part of that data
  3. Execute a command (optionally using the parameters as part of the command).
  4. Repeat for each data item
  5. If you are using the FOR command at the command line rather than in a batch program, use a single ‘%’ sign: %F

Save the batch script from Appendix B as a .bat file:

That’s it!

Wrapping up

In this article, we looked at how a PowerShell or a Batch script can be used to automate data restoration using a bacpac file using sqlpackage.exe. This tool is one of the easiest tools that can be used to refresh the database. The code can be further simplified and automated for various other database refresh needs.

Table of contents

Database Backup and Restore process in SQL Server – series intro
An overview of the process of SQL Server backup-and-restore
Understanding the SQL Server Data Management Life Cycle
Understanding SQL Server database recovery models
Understanding SQL Server Backup Types
Backup and Restore (or Recovery) strategies for SQL Server database
Discussing Backup and Restore Automation using SQLCMD and SQL Server agent
Understanding Database snapshots vs Database backups in SQL Server
SqlPackage.exe – Automate SQL Server Database Restoration using bacpac with PowerShell or Batch techniques
Smart database backup in SQL Server 2017
How to perform a Page Level Restore in SQL Server
Backup Linux SQL databases Using PowerShell and Windows Task Scheduler
SQL Server Database backup and restore operations using the Cloud
Tail-Log Backup and Restore in SQL Server
SQL Server Database Backup and Restore reports
Database Filegroup(s) and Piecemeal restores in SQL Server
In-Memory Optimized database backup and restore in SQL Server
Understanding Backup and Restore operations in SQL Server Docker Containers
Backup and Restore operations with SQL Server 2017 on Docker containers using Azure Data Studio
Interview questions on SQL Server database backups, restores and recovery – Part I
Interview questions on SQL Server database backups, restores and recovery – Part II
Interview questions on SQL Server database backups, restores and recovery – Part III
Interview questions on SQL Server database backups, restores and recovery – Part IV


Appendix (A)

PowerShell script

Appendix (B)

Batch script

Prashanth Jayaram
Backup and restore, PowerShell

About Prashanth Jayaram

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application. My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB. View all posts by Prashanth Jayaram