D365 FO DB restore in to cloud hosted Development environment using backup (.bacpac) file

 DB restore in to cloud hosted environment.

 

Import Database from UAT/Production to cloud-hosted or dev environment | D365 Finance and operations

 

To import a database to a developer environment after you've downloaded a database backup (.bacpac) file, you can begin the manual import operation on your Tier 1 environment. When you import the database, we recommend that you follow these guidelines:

 

·        Keep a copy of the existing AxDB database, so that you can revert to it later if needed.

·        Import the new database under a new name, such as AxDB_fromProd.

 

To ensure the best performance, copy the *.bacpac file to the local computer that you're importing from. Download sqlpackage .NET Core for Windows from Get sqlpackage .NET Core for Windows. Open a Command Prompt window, and run the following commands from the sqlpackage .NET Core folder.

 Note: 

Stop all the related dynamics services and w3 services.

SqlPackage.exe /a:import /sf:D:\Exportedbacpac\my.bacpac /tsn:localhost /tdn:<target database name> /p:CommandTimeout=1200  (old)

or 

SqlPackage /Action:Import /TargetServerName:"localhost" /TargetDatabaseName:"AdventureWorksLT" /TargetUser:"your_username" /TargetPassword:"your_password" /TargetTrustServerCertificate:True /SourceFile:"C:\AdventureWorksLT.bacpac" (new)

Eg: 

SqlPackage.exe /Action:Import /TargetServerName:"localhost" /TargetDatabaseName:"NDPC" /TargetTrustServerCertificate:True /SourceFile:"J:\MSSQL_BACKUP\ERPREPROD.bacpac" /p:CommandTimeout=6200

 

·        tsn (target server name) – The name of the Microsoft SQL Server instance 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.

Eg: 

SqlPackage.exe /Action:Import /TargetServerName:"localhost" /TargetDatabaseName:"NDPC" /TargetTrustServerCertificate:True /SourceFile:"J:\MSSQL_BACKUP\DFDSPRODCOPY.bacpac" /p:CommandTimeout=6200

(https://docs.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-download?view=sql-server-ver15)

Update the database

Run the following SQL script against the imported database. This script adds back the users that you deleted from the source database and correctly links them to the SQL logins for this SQL Server instance. The script also turns change tracking back on. Remember to edit the final ALTER DATABASE statement so that it uses the name of your database.

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

 

CREATE USER axretailruntimeuser FROM LOGIN axretailruntimeuser

 

CREATE USER axdeployextuser FROM LOGIN 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

 

DROP PROCEDURE IF EXISTS SP_ConfigureTablesForChangeTracking

DROP PROCEDURE IF EXISTS SP_ConfigureTablesForChangeTracking_V2

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

 

--Begin create retail channel database record--

declare @ExpectedDatabaseName nvarchar(64) = 'Default';

declare @DefaultDataGroupRecId BIGINT;

declare @ExpectedDatabaseRecId BIGINT;

IF NOT EXISTS (select 1 from RETAILCONNDATABASEPROFILE where NAME = @ExpectedDatabaseName)

BEGIN

               select @DefaultDataGroupRecId = RECID from RETAILCDXDATAGROUP where NAME = 'Default';

               insert into RETAILCONNDATABASEPROFILE (DATAGROUP, NAME, CONNECTIONSTRING, DATASTORETYPE)

               values (@DefaultDataGroupRecId, @ExpectedDatabaseName, NULL, 0);

               select @ExpectedDatabaseRecId = RECID from RETAILCONNDATABASEPROFILE where NAME = @ExpectedDatabaseName;

               insert into RETAILCDXDATASTORECHANNEL (CHANNEL, DATABASEPROFILE)

               select RCT.RECID, @ExpectedDatabaseRecId from RETAILCHANNELTABLE RCT

               inner join RETAILCHANNELTABLEEXT RCTEX on RCTEX.CHANNEL = RCT.RECID

        update RETAILCHANNELTABLEEXT set LIVECHANNELDATABASE = @ExpectedDatabaseRecId where LIVECHANNELDATABASE = 0

END;

--End create retail channel database record

 

Turn on change tracking

If change tracking was turned on in the source database, be sure to turn it on in the newly provisioned database in the target environment. To turn on change tracking, use the ALTER DATABASE command.

ALTER DATABASE [your database name] SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 6 DAYS, AUTO_CLEANUP = ON);

Start to use the new database

To switch environments and use the new database, first stop the following services:

·        World Wide Web Publishing Service

·        Microsoft Dynamics 365 Unified Operations: Batch Management Service

·        Management Reporter 2012 Process Service

After these services have been stopped, rename the AxDB database AxDB_orig, rename your newly imported database AxDB, and then restart the three services.

To switch back to the original database, reverse this process. In other words, stop the services, rename the databases, and then restart the services.

 

Example

 

ALTER DATABASE AxDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE

GO

ALTER DATABASE AxDB MODIFY NAME = AxDB_orig

GO

ALTER DATABASE AxDB_orig SET MULTI_USER;

GO

 

 

MS reference :

https://learn.microsoft.com/en-us/dynamics365/fin-ops-core/dev-itpro/database/dbmovement-scenario-exportuat#import-the-database