Commands to Import .bacpac file to D3FOE SQL Server

Introduction:

This blog article will explain how to import a .bacpac file to Microsoft SQL Server which is created from Finance and Operations database that is based on Azure SQL Server. You can refer steps here for creating .bacpac file.

Following points are recommended for smooth and secure importing of .bacpac file

1. Take a backup of existing database so you can revert if required.
2. Import the database with a new name and modify it later once the entire process is completed error free.
3. Copy the .bacpac file to local computer where you want to import the database for better performance.

Steps:

1. Run command prompt as an administrator.
2. Run the below command to Import the database.

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

   SqlPackage.exe /a:import /sf:D:\Exportedbacpac\SSProd.bacpac /tsn:localhost /tdn:SSProd /p:CommandTimeout=1200

   where,

   • tsn (target server name) – The name of the SQL Server to import into.
   • tdn (target database name) – The name of the database to import into. The database should not already exist.
   • sf (source file) – The path and name of the file to import from.
3. Update the database: Run the following script against your database to add the users you deleted while creating .bacpac file.  Update your database name in Alter Command.

   CREATE USER axdeployuser FROM LOGIN axdeployuser
   EXEC sp_addrolemember 'db_owner', 'axdeployuser'
   
   CREATE USER axdbadmin FROM LOGIN axdbadmin
   EXEC sp_addrolemember 'db_owner', 'axdbadmin'
   
   CREATE USER axmrruntimeuser FROM LOGIN axmrruntimeuser
   EXEC sp_addrolemember 'db_datareader', 'axmrruntimeuser'
   EXEC sp_addrolemember 'db_datawriter', 'axmrruntimeuser'
   
   CREATE USER axretaildatasyncuser FROM LOGIN axretaildatasyncuser
   EXEC sp_addrolemember 'DataSyncUsersRole', 'axretaildatasyncuser'
   
   CREATE USER axretailruntimeuser FROM LOGIN axretailruntimeuser
   EXEC sp_addrolemember 'UsersRole', 'axretailruntimeuser'
   EXEC sp_addrolemember 'ReportUsersRole', 'axretailruntimeuser'
   
   CREATE USER axdeployextuser WITH PASSWORD = '<password from LCS>'
   EXEC sp_addrolemember 'DeployExtensibilityRole', 'axdeployextuser'
   
   CREATE USER [NT AUTHORITY\NETWORK SERVICE] FROM LOGIN [NT AUTHORITY\NETWORK SERVICE]
   EXEC sp_addrolemember 'db_owner', 'NT AUTHORITY\NETWORK SERVICE'
   
   UPDATE T1
   SET T1.storageproviderid = 0
    , T1.accessinformation = ''
    , T1.modifiedby = 'Admin'
    , T1.modifieddatetime = getdate()
   FROM docuvalue T1
   WHERE T1.storageproviderid = 1 --Azure storage
   
   ALTER DATABASE [<your AX database name>] SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 6 DAYS, AUTO_CLEANUP = ON)
   GO
   -- Begin Refresh Retail FullText Catalogs
   DECLARE @RFTXNAME NVARCHAR(MAX);
   DECLARE @RFTXSQL NVARCHAR(MAX);
   DECLARE retail_ftx CURSOR FOR
   SELECT OBJECT_SCHEMA_NAME(object_id) + '.' + OBJECT_NAME(object_id) fullname FROM SYS.FULLTEXT_INDEXES
    WHERE FULLTEXT_CATALOG_ID = (SELECT TOP 1 FULLTEXT_CATALOG_ID FROM SYS.FULLTEXT_CATALOGS WHERE NAME = 'COMMERCEFULLTEXTCATALOG');
   OPEN retail_ftx;
   FETCH NEXT FROM retail_ftx INTO @RFTXNAME;
   
   BEGIN TRY
    WHILE @@FETCH_STATUS = 0 
    BEGIN 
    PRINT 'Refreshing Full Text Index ' + @RFTXNAME;
    EXEC SP_FULLTEXT_TABLE @RFTXNAME, 'activate';
    SET @RFTXSQL = 'ALTER FULLTEXT INDEX ON ' + @RFTXNAME + ' START FULL POPULATION';
    EXEC SP_EXECUTESQL @RFTXSQL;
    FETCH NEXT FROM retail_ftx INTO @RFTXNAME;
    END
   END TRY
   BEGIN CATCH
    PRINT error_message()
   END CATCH
   
   CLOSE retail_ftx; 
   DEALLOCATE retail_ftx; 
   -- End Refresh Retail FullText Catalogs

4. Run the re-provision tool: To ensure Retail components are functional run the re-provision tool. Find steps here on how to run the tool.
5. Start using the new database: Stop the below service to switch the database.
   • World wide web publishing service.
   • Finance and Operations Batch Management Service.
   • Management Reporter 2012 Process Service.

   Once the services are stopped, rename your original database to ‘AxDB_orig’ and the new database to ‘AxDB’. Restart the service.

Conclusion:

This is how you can create a new database and import the data from a .bacpac file. If you face any issue then you can switch to original database or restore the copy of original database created prior to import.