In working with Dynamics 365 for operations one is exposed to Azure SQL. Azur SAL works a bit differently than the “regular old” SQL and one of those areas is backup.

To backup a database in Azure SQL I use one of two methods:

1. Create a copy of the table in the same SQL Server

CREATE DATABASE [sourcedb] AS COPY OF [destinationdb]

This operation is performed asynchronously and you will need to verify that the job is done before you continue. This can be done with this command:

SELECT * FROM sys.dm_database_copies

Note: you need to have selected the master database when you run the backup. Otherwise you will not be able get an exclusive lock on the database and you will get the following error: Msg 42019

To find what is locking the database you can use:

SELECT * FROM sys.dm_exec_sessions
KILL [session ID]

2. Use SqlPackage to create an export in the form of a BacPac file

cd C:\Program Files (x86)\Microsoft SQL Server\130\DAC\bin
SqlPackage.exe /a:export /ssn:<server>.database.windows.net /sdn:<SourceDB> /tf:<filedestination> /p:CommandTimeout=1200 /p:VerifyFullTextDocumentTypesSupported=false /sp:<sql password> /su:<sql user>

/Johan