download

download

Hey guys in this blog I will tell you about creating a copy of your Microsoft Dynamics 365 For Finance and Operations Database.
First you will need to export the Finance and Operations database and to do that you will have to stop the services.

Log on to the environment, open up the services and stop the following services

  • World wide web publishing service (on all AOS computers)
  • Microsoft Dynamics 365 for Finance and Operations Batch Management Service (on non-private AOS computers only)
  • Management Reporter 2012 Process Service (on business intelligence [BI] computers only)

Run sqlpackage to export the Finance and Operations database

Open a Command Prompt window as an administrator from the folder “C:\Program Files (x86)\Microsoft SQL Server\130\DAC\bin”, and run the following commands.
SqlPackage.exe /a:export /ssn:<server>.database.windows.net /sdn:<database to export> /tf:D:\Exportedbacpac\my.bacpac /p:CommandTimeout=1200 /p:VerifyFullTextDocumentTypesSupported=false /sp:<SQL password> /su:<SQL user>

The command creates a .bacpac file in the D:\Exportedbacpac folder. By copying or uploading this file to secure location, you can import it into another environment later.

Now you will have to start the services you stopped earlier

You can Import the .bacpac file to another location. To do that you will have to stop the services

Log on to the environment, open up the services and stop the following services

  • World wide web publishing service (on all AOS computers)
  • Microsoft Dynamics 365 for Finance and Operations Batch Management Service (on non-private AOS computers only)
  • Management Reporter 2012 Process Service (on business intelligence [BI] computers only)

After that you can Import the .bacpac file that was exported earlier.

Copy the .bacpac file to the AOS computer in the target environment.

Open a Command Prompt window as an administrator from the folder “C:\Program Files (x86)\Microsoft SQL Server\130\DAC\bin\” and run the following commands.
SqlPackage.exe /a:import /sf:D:\Exportedbacpac\my.bacpac /tsn:<Azure DSQL database server name>.database.windows.net /tu:sqladmin /tp:<password from LCS> /tdn:<new database name> /p:CommandTimeout=1200 /p:DatabaseEdition=Premium /p:DatabaseServiceObjective=<See below>

 

Run the following script to update the Finance and Operations database. This script will drop the users in the database and recreate users with correct passwords in the environment.

 

ALTER AUTHORIZATION ON Fulltext Catalog::[<name of the full text catalog in your database] TO [dbo];

declare @userSQL varchar(1000)

set quoted_identifier off

declare userCursor CURSOR for

select ‘DROP USER ‘ + name

from sys.sysusers

where issqlrole = 0 and hasdbaccess = 1 and name <> ‘dbo’

OPEN userCursor

FETCH userCursor into @userSQL

WHILE @@Fetch_Status = 0

BEGIN

exec(@userSQL)

FETCH userCursor into @userSQL

END

CLOSE userCursor

DEALLOCATE userCursor

CREATE USER axdeployuser FROM LOGIN axdeployuser

EXEC sp_addrolemember ‘db_owner’, ‘axdeployuser’

CREATE USER axdbadmin WITH PASSWORD = ‘<password from LCS>’

EXEC sp_addrolemember ‘db_owner’, ‘axdbadmin’

CREATE USER axruntimeuser WITH PASSWORD = ‘<password from LCS>’

EXEC sp_addrolemember ‘db_datareader’, ‘axruntimeuser’

EXEC sp_addrolemember ‘db_datawriter’, ‘axruntimeuser’

CREATE USER axmrruntimeuser WITH PASSWORD = ‘<password from LCS>’

EXEC sp_addrolemember ‘ReportingIntegrationUser’, ‘axmrruntimeuser’

EXEC sp_addrolemember ‘db_datareader’, ‘axmrruntimeuser’

EXEC sp_addrolemember ‘db_datawriter’, ‘axmrruntimeuser’

CREATE USER axretailruntimeuser WITH PASSWORD = ‘<password from LCS>’

EXEC sp_addrolemember ‘UsersRole’, ‘axretailruntimeuser’

EXEC sp_addrolemember ‘ReportUsersRole’, ‘axretailruntimeuser’

CREATE USER axdeployextuser WITH PASSWORD = ‘<password from LCS>’

EXEC sp_addrolemember ‘DeployExtensibilityRole’, ‘axdeployextuser’

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

 

After following these steps you can duplicate the database to different environment. So that’s it from my blog, will be back with more.

 

source: https://docs.microsoft.com/en-us/dynamics365/unified-operations/dev-itpro/database/copy-operations-database

Leave a Reply

Recent Comments

    Archives

    Categories