Skip to main content

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

Update: If following method is not working have a look at this Changes in SQL Script to restore bacpac file for D365FO

Many times to debug the Production environment issues we might need live data to address that issue in such case first of all we need to refresh the UAT/Sandbox database with the Production environments database and then afterward we need to export the UAT database to the Asset library from where we can get bacpac file for that UAT database which is then to be imported in your cloud-hosted or one-box environment. This blog and video demonstration will help you to restore your production or UAT database to a cloud-hosted environment.



Step 1: Rename existing db

 In this step, we need to rename the existing AxDB for safety purposes. Just go to the SSMS application and execute the following command:-

 

ALTER DATABASE [ip_ent_site] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

GO

ALTER DATABASE [ip_ent_site] MODIFY NAME = [ip_ent_site_new]

GO

ALTER DATABASE [ip_ent_site_new] SET MULTI_USER;

GO

 

Example

 

ALTER DATABASE AxDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE

GO

ALTER DATABASE AxDB MODIFY NAME = AxDB_Old

GO

ALTER DATABASE AxDB_Old SET MULTI_USER;

GO

 

 

Step 2: Get sql.net tool install it


Download latest sql.net package because sometimes existing SQL package will throw an error that database bacpac file is courrpted, so to avoid such scenarios download and install latest package from the following link

 

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


Then change directory till installer and run the following command:

 

[sql.net package directory]\Sqlpackage


Example


C:\Users\Admin6680303d7e\Downloads\sqlpackage-win7-x64-en-US-15.0.4897.1\sqlpackage

 

Step 3: Install bapac file


Once you are done with the previous two steps now proceed with the command to install bacpac file as follows:-


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


Example

"C:\Program Files (x86)\Microsoft SQL Server\130\DAC\bin\SqlPackage.exe" /a:import /sf:"C:\Users\Admin6680303d7e\Downloads\Sandboxbackup.bacpac" /tsn:localhost /tdn:AxDB /p:CommandTimeout=50000

Note:- If command is not working in recent version try with the following command 

SqlPackage.exe /a:import /sf:"C:\Users\Admina1cb735e26\Downloads\nestle-uatbackup.bacpac" /tsn:localhost /tdn:AxDB_UAT /ttsc:true /p:CommandTimeout=5000


Note: Sometimes we need to increase the CommandTimeOut value depending on your database size to avoid timeout error.

  

Step 4 : Update the database 

Now we have successfully imported the database we need to restore some of its configuration for which we need to execute the script provided by Microsoft in the following link with the heading update the database.

after this, we are ready to use the environment in case there is retail data or configurations you would need to execute the Reprovision tool.


I hope this blog was helpful to you. You can also find out more interesting videos at my channel Root check or  D365fo playlist.





x

Labels:

Comments

Post a Comment

Popular posts from this blog

Changes in SQL Script to restore bacpac file for D365FO

There are changes in SQL db import it needs additional parameters now  Connection Security Improvements in SqlPackage 161 | Microsoft Community Hub otherwise it will throw following error *** Changes to connection setting default values were incorporated in a recent release.  More information is available at https://aka.ms/dacfx-connection *** Error importing database:Could not import package. Unable to connect to target server 'localhost'. Please verify the connection information such as the server name, login credentials, and firewall rules for the target server. A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.) The certificate chain was issued by an authority that is not trusted. *** The settings for connection encryption or server certificate trust may lead to connection failure if the server is not properl...
Post a Comment
Read more

Resolve "More Than One Form Was Opened at Once for the Lookup Control" Error

While overriding lookup method on  form data source's field it COC is not working for most of the case so everybody is looking after event handlers. But there is one problem in case of that after writing code for event handler its throwing error as "More Than One Form Was Opened at Once for the Lookup Control" as you can see in following screenshot. To resolve this we need to cancel super call to lookup method which is fairly easy to acheive you just need to add following two lines of code in such case  1 2 3 // Cancel the super call of lookup control. FormControlCancelableSuperEventArgs formControlCancelSuper = e as FormControlCancelableSuperEventArgs; formControlCancelSuper.CancelSuperCall(); for example: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 [FormControlEventHandler(formControlStr(PurchTable, ContactInfo_Email), FormControlEventType::Lookup)] public static void ContactInfo_Email_On...
Post a Comment
Read more