Sometimes we have a situation where we need to restore data from tier 1 environments to tier 2 environments. Example of such a scenario is stored configuration data in Tier 1 instance which we want to load in tier 2 instance.
You can follow below approach to export tier 1 DB to bacpac file.
First approach is through exporting data using below command
C:\Program Files (x86)\Microsoft SQL Server\130\DAC\bin
SqlPackage.exe /a:export /ssn:localhost /sdn:CopyOFAXDB /tf:J:\MSSQL_BACKUP\AxDB_12072021.bacpac /p:CommandTimeout=1200 /p:VerifyFullTextDocumentTypesSupported=false
You can install SqlPackage.exe from here.
In the above command CopyOFAXDB is D365 F&O database which we want to export and AxDB_12072021.bacpac is the name of bacpac file.
Timeout of 1200 sec is specified to avoid below error.
Another approach to export your database is using SQL server management studio. However, with this approach also you get same error as shown in the previous image.
To avoid this error, you can perform following change in the registry to increase the timeout.
Open registry editor and visit following path – Computer\HKEY_CURRENT_USER\Software\Microsoft\VisualStudio\10.0\SQLDB\Database
Select value QueryTimeoutSeconds and increase value to 1200 or higher if you are facing timeouts even after specifying 1200 s restart VM and try exporting again using below steps.
You can select your database in SQL server management studio and click on DB which you want to export as a bacpac and select export data tier application option as shown in below image.
Once you select this option, you will get a introduction screen which you can skip and go the export settings.
You have two options to save your bacpac file
- You can save to the local disk
- Directly save in your storage account in Azure.
Just follow the wizard after you select your option to save the file and wait for the export to finish. After file export is finished you can see what has been exported as a part of the export
You can upload this file in LCS asset library and apply to your tier 2 environment