Creating and restoring bacpac files without using a GUI

Almost all databases I use are SQL Server databases. They are created with hand written SQL scripts and upgraded with hand written SQL scripts - it is very rare I'll use SQL Server Management Studio's (SSMS) designers to work with database objects. When backing up or restoring databases, I have various SQL scripts to do this, which works fine when SQL Server has access to your file system, or you theirs.

This isn't always the case. Last year I replaced our woefully inadequate error logging system with something slightly more robust and modern, and this system is hosted on Microsoft's Azure platform using SaaS. No direct file access there!

Rather than using traditional database backups, for Azure hosted databases you need to use Data-tier Applications. While these do serve more advanced purposes than traditional backups, in my scenario I am simply treating them as a means of getting a database from A to B.

SSMS allows you to work with these files, but only via GUI commands - there's no SQL statements equivalent to BACKUP DATABASE or RESTORE DATABASE, which is a royal pain. Although I have my Azure database backed up to blog storage once a week, I want to make my own backups more frequently, and be able to restore these locally for development work and performance profiling. Doing this using SQL Server's GUI tools is not conductive to an easy workflow.

A CLI for working with BACPAC files

Fortunately, as I work with Visual Studio I have the SQL Server Data Tools (SSDT) installed, which includes SqlPackage.exe, a magical tool that will let me import and export BACPAC files locally and remotely.

Less fortunately, it isn't part of the path and so we can't just merrily type sqlpackage into a command window the same way you can type sqlcmd and expect it to work; it won't. And it doesn't seem to have a convenient version-independent way of grabbing it from the registry either. On my machine it is located at C:\Program Files (x86)\Microsoft SQL Server\120\DAC\bin, but this may change based on what version of the tools you have installed.

Creating a BACPAC file from an existing database

To export a database into a BACPAC file, you can run the following command. Note that this works for databases on a local/remote SQL Server instance or Azure SQL Database.

bat

sqlpackage.exe /a:Export /ssn:<ServerName> /sdn:<DatabaseName> /su:<UserName> /sp:<Password> /tf:<ExportFileName>

Listed below are the arguments we're using. In my example above, I'm using the short form, you can use either long or short forms to suit your needs.

/Action (a) - the action to perform, in this case Export
/SourceServerName (ssn) - the source server name. Can be either the URI of an Azure database server, or the more traditional ServerName\InstanceName
/SourceDatabaseName (sdn) - the name of the database to export
/SourceUser (su) - the login user name
/SourcePassword (sp) - the login password

For trusted connections, you can skip the su and sp arguments.

Exporting an Azure SQL Database to a data-tier application file via the command line

The screenshot above shows typical output.

Restoring a database from a BACPAC file

Restoring a database is just as easy, just use an action of Import instead of export, and invert source and target in arguments.

cmd

sqlpackage.exe /a:Import /tsn: /tdn: /tu: /tp: /sf:

There are a couple of caveats however - if the target database already exists and contains objects such as tables or views, then the import will fail. The database must either not exist, or be completely empty.

Sadly, despite the fact that you have separate source and target arguments, it doesn't appear to be possible to do a direct copy from the source server to the target server.

Importing a data-tier application into a local SQL Server instance from a BACPAC file via the command line

An automated batch script for restoring a database

The following batch file is a simple script I use to restore the newest available bacpac file in a given directory. The script also deletes any existing local database using sqlcmd prior to importing the database via sqlpackage, resolving a problem where non-empty SQL databases can't be restored using the package tool.

It's a very simple script, and not overly robust but it does the job I need it to do. I still tend to use batch files over PowerShell for simple tasks, no complications about loaded modules, slow startup, just swift execution without fuss.

cmd

@ECHO OFF SETLOCAL REM This is the directory where the SQL data tools are installed SET SQLPCKDIR=C:\Program Files (x86)\Microsoft SQL Server\120\DAC\bin\ SET SQLPCK="%SQLPCKDIR%SqlPackage.exe" REM The directory where the bacpac files are stored SET DBDIR=D:\Backups\azuredbbackups\ REM The name of the database to import SET DBNAME=MyDatabase REM The SQL Server name / instance SET SERVERNAME=. REM SQL statement to delete the import database as SQLPACKAGE won't import to an existing database SET DROPDATABASESQL=IF EXISTS (SELECT * FROM [sys].[databases] WHERE [name] = '%DBNAME%') DROP DATABASE [%DBNAME%]; REM Try and find the newest BACPAC file FOR /F "tokens=*" %%a IN ('DIR %DBDIR%*.bacpac /B /OD /A-D') DO SET PACNAME=%%a IF "%PACNAME%"=="" GOTO :bacpacnotfound SET DBFILE=%DBDIR%%PACNAME% SQLCMD -S %SERVERNAME% -E -Q "%DROPDATABASESQL%" -b IF %errorlevel% NEQ 0 GOTO :error %SQLPCK% /a:Import /sf:%DBFILE% /tdn:%DBNAME% /tsn:%SERVERNAME% IF %errorlevel% NEQ 0 GOTO :error GOTO :done :bacpacnotfound ECHO No bacpac file found to import. EXIT /B 1 :error ECHO Failed to import bacpac file. EXIT /B 1 :done ENDLOCAL