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
sqlpackage into a command window the same way you
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
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.
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.
a) - the action to perform, in this case Export
ssn) - the source server name. Can be either the URI of an Azure database server, or the more traditional ServerName\InstanceName
sdn) - the name of the database to export
su) - the login user name
sp) - the login password
For trusted connections, you can skip the
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.
sqlpackage.exe /a:Import /tsn:<ServerName> /tdn:<DatabaseName> /tu:<UserName> /tp:<Password> /sf:<ExportFileName>
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.
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
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.
@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
- 2016-06-18 - First published
- 2020-11-21 - Updated formatting