Hi Folks,
All tier 2 server we have to use bacpac file to restore a
database from tier 1 server which has .bak file when doing a DB export. In this
post, we will see how to restore the database from tier 1 server to tier 2 server.
(Know more about Cloud offering and different tiers)
Step 1: Create a copy of your DB.
Use below script,
BACKUP DATABASE
[AxDB] TO DISK = N'D:\Backups\axdb_golden.bak' WITH
NOFORMAT, NOINIT,
NAME = N'AxDB_golden-Full Database
Backup', SKIP, NOREWIND, NOUNLOAD,
COMPRESSION, STATS = 10
GO
RESTORE DATABASE
[AxDB_CopyForExport] FROM DISK = N'D:\Backups\axdb_golden.bak' WITH FILE = 1,
MOVE N'AXDBBuild_Data' TO N'F:\MSSQL_DATA\AxDB_CopyForExport.mdf',
MOVE N'AXDBBuild_Log' TO N'G:\MSSQL_LOGS\AxDB_CopyForExport_Log.ldf',
NOUNLOAD, STATS = 5
Choose master from the dropdown, so the new DB will create on the root folder on inside the AXDB. Make sure you update all the paths as per your
machine. This script must create a new database named AxDB_CopyForExport.
Step 2: Now we need to make some changes in this Database. You
should run this script for a successful Export/Import of database.
Here is the complete script, which you need to run on new DB i.e. AxDB_CopyForExport .
update sysglobalconfiguration
set value = 'SQLAZURE'
where name = 'BACKENDDB'
update sysglobalconfiguration
set value = 1
where name = 'TEMPTABLEINAXDB'
drop procedure if exists
XU_DisableEnableNonClusteredIndexes
drop procedure if exists SP_ConfigureTablesForChangeTracking
drop procedure if exists
SP_ConfigureTablesForChangeTracking_V2
drop schema [NT
AUTHORITY\NETWORK SERVICE]
drop user [NT
AUTHORITY\NETWORK SERVICE]
drop user
axdbadmin
drop user
axdeployuser
drop user axmrruntimeuser
drop user
axretaildatasyncuser
drop user
axretailruntimeuser
drop user
axdeployextuser
--Tidy up the batch server config from the previous
environment
DELETE FROM
SYSSERVERCONFIG
--Tidy up server sessions from the previous environment
DELETE FROM
SYSSERVERSESSIONS
--Tidy up printers from the previous environment
DELETE FROM
SYSCORPNETPRINTERS
--Tidy up client sessions from the previous environment
DELETE FROM
SYSCLIENTSESSIONS
--Tidy up batch sessions from the previous environment
DELETE FROM
BATCHSERVERCONFIG
--Tidy up batch server to batch group relation table
DELETE FROM
BATCHSERVERGROUP
-- Clear encrypted hardware profile merchant properties
update
dbo.RETAILHARDWAREPROFILE set SECUREMERCHANTPROPERTIES = null where SECUREMERCHANTPROPERTIES is not null
Above scrip will;
I. Set the SysGlobalConfiguration flag to inform the application that the database is Azure-based.
II. Remove a reference to tempDB in the XU_DisableEnableNonClusteredIndexes procedure. References to tempDB aren't allowed in an Azure SQL database. The database synchronization process will re-create the reference later.
III. Drop users, because Microsoft Windows users are forbidden in Azure SQL databases. Other users must be re-created later so that they're correctly linked to the appropriate sign-in on the target server.
IV. Clear encrypted hardware profile merchant properties.
Step 3: Export database as bacpac file.
Open CMD as admin (it will work without cmd as well), and go to path “C:\Program Files
(x86)\Microsoft SQL Server\140\DAC\bin\”
Run below command,
SqlPackage.exe /a:export /ssn:localhost /sdn: AxDB_CopyForExport
/tf: J:\MSSQL_BACKUP\GoldenConfig01082020.bacpac /p:CommandTimeout=1200
/p:VerifyFullTextDocumentTypesSupported=false
·
\ ssn (source server name) – The name of the SQL Server to export
from. For the purposes of this topic, the name should always be localhost.
·
sdn
(source database name) –
The name of the database to export.
·
tf
(target file) – The path and
name of the file to export to. The folder should already exist, but the export the process will create the file.
Check all the default location carefully and run it. Don’t put any space while putting these values, (refer below image)
else it will throw an error
Step 4: Upload bacpac file to LCS
Login into lcs.dynamics.com and Go to assets library > database backup > click on + button
Select your file and select the backup type as ‘Product database
(SQL Server or .bak)’ and upload this file.
Step 5: Import database to target tier 2 box.
Go to LCS and select a target T-2 box,
Click on details
Go to maintenance > Move database > Import a bacpac >
select your file from the asset library
Further reference; Click here.
Cheers!!!
Harry
