Blog Articles and information on C# and .NET development topics

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.

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.

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.

• /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.

The screenshot above shows typical output.

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.

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.

@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

While we appreciate comments from our users, please follow our posting guidelines. Have you tried the Cyotek Forums for support from Cyotek and the community?