如何把Dynamics 365 FO UAT的数据库还原到开发环境(how-to-restore-d365fo-sandbox-db-to-dev)

还原数据库

以下是将 Sandbox DB 恢复到 DEV 的过程。

  1. LCS -> UAT -> Maintain -> Move Database -> Export database
  2. bacpac文件会被导出到资产库
  3. 从资产库下载bacpac文件
  4. 下载SqlPackage:sqlpackage-win7-x64-en-16.0.6161.0.zip
  5. 在cmd中运行下列命令
SqlPackage.exe /a:import /sf:D:\Exportedbacpac\my.bacpac /tsn:localhost /tdn:<target database name> /p:CommandTimeout=1200
Example
Demo
1.	SqlPackage.exe /a:import /sf:J:\MSSQL_BACKUP\Demo_PreProdbackup.bacpac /tsn:localhost /tdn:PreProd_20220712 /p:CommandTimeout=1200
Demo
2.	SqlPackage.exe /a:import /sf:J:\MSSQL_BACKUP\Demo_uatbackup.bacpac /tsn:localhost /tdn:uat_20221028 /p:CommandTimeout=1200
  1. 等待
  2. 数据库还原成功后运行以下脚本并且更新数据库的名字
ALTER DATABASE [uatbackup_20190401.bacpac] SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 6 DAYS, AUTO_CLEANUP = ON)
 
--
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
EXEC sp_addrolemember 'DataSyncUsersRole', 'axretaildatasyncuser'
 
CREATE USER axretailruntimeuser FROM LOGIN axretailruntimeuser
EXEC sp_addrolemember 'UsersRole', 'axretailruntimeuser'
EXEC sp_addrolemember 'ReportUsersRole', 'axretailruntimeuser'
 
CREATE USER axdeployextuser FROM LOGIN axdeployextuser
EXEC sp_addrolemember 'DeployExtensibilityRole', '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
 
ALTER DATABASE [uatbackup_20190401.bacpac] SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 6 DAYS, AUTO_CLEANUP = ON)
GO
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
==
  1. 停止所有D365FO的服务
  2. 重命名AXDB -> AXDB_OldYYYYMMDD
  3. 重命名还原好的数据库 -> AXDB
  4. 打开VS做数据库同步
  5. 启动所有D365FO的服务