Restore Database User Guide

This section contains the steps to restore the azure database into your VM’s

 

1.1     Latest version of SQL server management studio

Install the latest version of SQL Server Management Studio in your local VM:

https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms

Select the highlighed option (NOT the upgrade package):

 

1.2     Azure Database restore on VM

Please follow below steps on your local VM to intiate the restore

·         Open command prompt with administrative privileges.

·         Run the following scripts

 

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

b)    SqlPackage.exe /a:import /sf: C:\Temp\FAT.bacpac /tsn:localhost /tdn:FATCopy /p:CommandTimeout=1200

Replace D:\ with the path where you saved the bacpac file.

·         The backup file will be imported into your local VM sql instance (it will take a while)

 

1.3     Running scripts on restored Database

 

1. Run this script against the imported database (FATCopy) 

 

a)    CREATE USER axdeployuser FROM LOGIN axdeployuser

·         EXEC sp_addrolemember 'db_owner', 'axdeployuser'

 

b)    CREATE USER axdbadmin FROM LOGIN axdbadmin

EXEC sp_addrolemember 'db_owner', 'axdbadmin'

 

c)     CREATE USER axmrruntimeuser FROM LOGIN axmrruntimeuser

EXEC sp_addrolemember 'db_datareader', 'axmrruntimeuser'

EXEC sp_addrolemember 'db_datawriter', 'axmrruntimeuser'

 

d)    CREATE USER axretaildatasyncuser FROM LOGIN axretaildatasyncuser

EXEC sp_addrolemember 'DataSyncUsersRole', 'axretaildatasyncuser'

 

 

e)    CREATE USER axretailruntimeuser FROM LOGIN axretailruntimeuser

EXEC sp_addrolemember 'UsersRole', 'axretailruntimeuser'

EXEC sp_addrolemember 'ReportUsersRole', 'axretailruntimeuser'

 

f)      CREATE USER [NT AUTHORITY\NETWORK SERVICE] FROM LOGIN [NT AUTHORITY\NETWORK SERVICE]

EXEC sp_addrolemember 'db_owner', 'NT AUTHORITY\NETWORK SERVICE'

 

g)    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 [FATCopy] SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 6 DAYS, AUTO_CLEANUP = ON)

 

1.4     Copy data from AxDB (Original) to FATCopy (New restored)

Run these scripts to copy UserInfo and SysServiceConfigurationSettings table from original AxDB to newly restored FATCopy azure database.

·         Use [AxDB]

Select [Value] from SYSSERVICECONFIGURATIONSETTING

Where [Name] = ‘TENANTID’

 

Copy this value

 

·         Use [FATCopy]

Update SYSSERVICECONFIGURATIONSETTING

Set Value = ‘Copied in above step’

Where [Name] = ‘TENANTID’

 

·         Use [AxDB]

Select [SID] from USERINFO WHERE ID='Admin'

           

Copy this value

 

·         Use [FATCopy]

Update USERINFO

Set [SID] = ‘Copied in above step’

Where [ID] = ‘Admin'

 

1.5     Rename the new restored Database

·         Stop Word wide web pulishing , D365 Batch management , DIEF , MR services.

·         Give a meaningful name to existing AxDB i.e AxDB_Orig

·         Rename FATCopy to AxDB.

·         Start all the services.

·         Add your @hsdyn.com Id as Admin using AdminUserProvisioning tool.