Commands to Create .bacpac file from Azure SQL to SQL Server

Introduction:

This topic explains how to export a Microsoft Dynamics 365 for Finance and Operations, Enterprise edition database from an environment that is based on Microsoft Azure.

Steps:

1. Create a duplicate of the source database.

<"CREATE DATABASE MyNewCopy AS COPY OF axdb_mySourceDatabaseToCopy">

To monitor the progress of the copy operation, run the following query against the MASTER database in the same instance.

2. Run the Sql Server script to Prepare the database.

--Prepare a database in SQL Azure for export to SQL Server.

--Disable change tracking on tables where it is enabled.

declare

@SQL varchar(1000)

set quoted_identifier off

declare changeTrackingCursor CURSOR for

select 'ALTER TABLE ' + t.name + ' DISABLE CHANGE_TRACKING'

from sys.change_tracking_tables c, sys.tables t

where t.object_id = c.object_id

OPEN changeTrackingCursor

FETCH changeTrackingCursor into @SQL

WHILE @@Fetch_Status = 0

BEGIN

exec(@SQL)

FETCH changeTrackingCursor into @SQL

END

CLOSE changeTrackingCursor

DEALLOCATE changeTrackingCursor

--Disable change tracking on the database itself.

ALTER DATABASE

-- SET THE NAME OF YOUR DATABASE BELOW

MyNewCopy

set CHANGE_TRACKING = OFF

--Remove the database level users from the database

--these will be recreated after importing in SQL Server.

declare

@userSQL varchar(1000)

set quoted_identifier off

declare userCursor CURSOR for

select 'DROP USER ' + name

from sys.sysusers

where issqlrole = 0 and hasdbaccess = 1 and name <> 'dbo'

OPEN userCursor

FETCH userCursor into @userSQL

WHILE @@Fetch_Status = 0

BEGIN

exec(@userSQL)

FETCH userCursor into @userSQL

END

CLOSE userCursor

DEALLOCATE userCursor

--Delete the SYSSQLRESOURCESTATSVIEW view as it has an Azure-specific definition in it.

--We will run db synch later to recreate the correct view for SQL Server.

if(1=(select 1 from sys.views where name = 'SYSSQLRESOURCESTATSVIEW'))

DROP VIEW SYSSQLRESOURCESTATSVIEW

--Next, set system parameters ready for being a SQL Server Database.

update sysglobalconfiguration

set value = 'SQLSERVER'

where name = 'BACKENDDB'

update sysglobalconfiguration

set value = 0

where name = 'TEMPTABLEINAXDB'

--Clean up the batch server configuration, server sessions, and printers from the previous environment.

TRUNCATE TABLE SYSSERVERCONFIG

TRUNCATE TABLE SYSSERVERSESSIONS

TRUNCATE TABLE SYSCORPNETPRINTERS

--Remove records which could lead to accidentally sending an email externally.

UPDATE SysEmailParameters

SET SMTPRELAYSERVERNAME = ''

GO

UPDATE LogisticsElectronicAddress

SET LOCATOR = ''

WHERE Locator LIKE '%@%'

GO

TRUNCATE TABLE PrintMgmtSettings

TRUNCATE TABLE PrintMgmtDocInstance

--Set any waiting, executing, ready, or canceling batches to withhold.

UPDATE BatchJob

SET STATUS = 0

WHERE STATUS IN (1,2,5,7)

GO

3. Export the database from Azure SQL

cd C:\Program Files (x86)\Microsoft SQL Server\140\DAC\bin

SqlPackage.exe /a:export /ssn:.database.windows.net /sdn:MyNewCopy /tf:D:\Exportedbacpac\my.bacpac /p:CommandTimeout=1200 /p:VerifyFullTextDocumentTypesSupported=false /sp:EG=ajgU8!Fx=gY /su:sqladmin

Where :

• ssn (source server name) – The name of the Azure SQL Database server to export from.
• sdn (source database name) – The name of the database to export.
• tf (target file) – The path and name of the file to export to.
• sp (source password) – The SQL password for the source SQL Server.
• su (source user) – The SQL user name for the source SQL Server. We recommend that you use the sqladmin