Recreating AX Environments / Moving Databases

Overview

On a frequent basis I am being requested to recreate / move AX databases between environments.

DEV to UAT.
PROD to UAT.
GOLD to LOCAL.
PROD to LOCAL etc.

This is not officially supported by MS, they recommended that the DB’s never move because there are so many references to settings on a per environment basis that you are likely to cause problems. 
Hence we never ever move a DB to PROD, we can move from and to any other platform for testing and development, but never to PROD. 
​In most cases, this is not a problem but there are circumstances that need to be considered to get the restored database functioning.

For example, I cloned the UAT DB onto the Developers machines, when they were adding code and compiling it, there was still references to the UAT AOS server which in turn caused the UAT environment to have wrong code installed on it and also the compilation caused the CPU and Disk to max out on UAT.

The following scenarios are where we may need to restore a Microsoft Dynamics AX database to a different environment:
  • Testing a Microsoft Dynamics AX service pack, roll-up, or full version upgrade.
  • Bringing Microsoft Dynamics AX 2012 data in house for testing or development.
  • Restoring a copy of the production database into a test or development environment to work with the most recent changes.

Script Info

I have created a Powershell script to perform the action of moving AX Databases between environments in a safe manner ensuring all the relevant server name values are changed to reflect the new destination environment.

It performs the following actions.

  •   Step 1 : Check for Local Admin Rights
  •   Step 2 : Take new $Source AX Backup
  •   Step 3 : Stop AOS on Target Environment
  •   Step 4 : Backup $Target AX Databases
  •   Step 5 : Restore $Source DB to $Target DB
  •   Step 6 : Reconfigure $Target settings (i.e change server names and service accounts etc)
  •   Step 7 : Delete XppIL Cache on $Target
  •   Step 8 : Restart AOS on $Target
  •   Step 9 : Couple of minute time-out to allow the service to fully start and the next steps complete successfully 
  •   Step 10 : Check Business Connector Install and Install if not present.
  •   Step 11 : If LOCAL or GOLD Desktop Devs are Admin and access is recreated
  •   Step 12 : If DEV or CHDEV – Needs DEV Admin Group

You can pass through the following Parameters to the script :

  • Target – LOCAL, DEV, CHDEV, GOLD
  • Source – DEV, UAT, PROD, GOLD, CHDEV
  • RunHeadless – If this value is passed it runs silently
  • If you pass through no Parameters it will prompt for a Source and Target Environment. 

Examples of script usage :

  • “Recreate_AX_Env.ps1 -Target LOCAL -Source PROD -RunHeadless”
  • Recreate_AX_Env.ps1 -Target CHDEV -Source UAT -RunHeadless”

If the script is called with no parameters it prompts for a Source and Target Environment.

Dependant upon the Source, it must be ran as a SysAdmin of Source Environment in order to perform the action of promoting or demoting Admins for the Target Env.

Running Script

The script needs to be ran as Admin, so I have created a Batch File to call it with. Run the Batch file as Admin on the destination AOS you want to have the new DB’s.

Recreate_AX_Env.ps1 -Target LOCAL -Source PROD -RunHeadless

You will be presented with the following screen first to choose the Source Environment (not case sensitive) :

Capture

You will be presented with the following screen second to choose the Target Environment (not case sensitive) :

Capture

A final warning is presented before the process continues :

Capture

It roughly takes 30 – 40 minutes to run through the script depending upon performance and location of data.

param(
[ValidateSet("local","dev","chdev","gold",ignoreCase = $true)]
[Parameter(Mandatory = $false)]
[string]$Target,
[ValidateSet("dev","uat","prod","gold","chdev",ignoreCase = $true)]
[Parameter(Mandatory = $false)]
[string]$Source,
[switch]$RunHeadless
)</p>
################################################################
# Recreate_AX_Env.ps1
#
# A powershell script for restoring and reconfiguring AX backups
# to any AX Environment.
#
################################################################
# Functions / Methods
################################################################

function Test-AdministratorPrivileges
{
$identity = [System.Security.Principal.WindowsIdentity]::GetCurrent()
$principal = New-Object System.Security.Principal.WindowsPrincipal ($identity)
$principal.IsInRole([System.Security.Principal.WindowsBuiltInRole]::Administrator)
}

function Reconfigure-AxConfiguration
{

param(
[Parameter(Mandatory = $true)]
[string]$ComputerName,
[Parameter(Mandatory = $true)]
[string]$SqlServerInstance,
[Parameter(Mandatory = $true)]
[string]$DestTransDB,
[Parameter(Mandatory = $true)]
[string]$SourceTransDB,
[Parameter(Mandatory = $true)]
[string]$ReportServerHost

)

process
{
Write-Host
Write-Host "Updating AX $target settings...."

Invoke-Sqlcmd -QueryTimeout 65535 -ServerInstance $SqlServerInstance -Query "
DECLARE @PartitionId bigint;

-- Get ID of the partition..
SELECT @PartitionId = RECID FROM [$DestTransDB].[dbo].[PARTITIONS] WHERE PARTITIONKEY='initial';

-- Drop Server GUID so that the Cache refreshes etc
UPDATE [$DestTransDB].[dbo].[SysSQMSettings] Set GlobalGUID = '{00000000-0000-0000-0000-000000000000}'

-- Reconfigure UAT to local..
UPDATE [$DestTransDB].[dbo].[SYSGLOBALCONFIGURATION] SET VALUE='http://${ComputerName}:80/DynamicsAX6HelpServer/HelpService.svc' WHERE NAME='HelpServerLocation';

TRUNCATE TABLE [$DestTransDB].[dbo].[SYSSERVERCONFIG];
TRUNCATE TABLE [$DestTransDB].[dbo].[SYSSERVERSESSIONS];
TRUNCATE TABLE [$DestTransDB].[dbo].[SYSCLIENTSESSIONS];

TRUNCATE TABLE [$DestTransDB].[dbo].[SRSSERVERS];
INSERT INTO [$DestTransDB].[dbo].[SRSSERVERS] (SERVERID, ISDEFAULTREPORTMODELSERVER, SERVERURL, ISDEFAULTREPORTLIBRARYSERVER, AXAPTAREPORTFOLDER, REPORTMANAGERURL, SERVERINSTANCE, AOSID, CONFIGURATIONID, ISSHAREPOINTINTEGRATED, RECVERSION, RECID)
VALUES ('$ReportServerHost',  0, 'http://$($ReportServerHost)/ReportServer_AXDEV', 1, 'DynamicsAx', 'http://$($ReportServerHost)/Reports_AXDEV', 'AXDEV', '01@$($ReportServerHost)',  '01@$($ReportServerHost)',  0, 1,  1 )

TRUNCATE TABLE  [$DestTransDB].[dbo].[BIANALYSISSERVER];
INSERT INTO [$DestTransDB].[dbo].[BIANALYSISSERVER] (SERVERNAME, DESCRIPTION, ISVALID, ISDEFAULT, DEFAULTDATABASENAME, PARTITIONS, RECVERSION, RECID)
VALUES ('$ReportServerHost', 'Dynamics AX analysis server', 1, 1, 'Dynamics AX initial', @PartitionId, 1, 1);

TRUNCATE TABLE [$DestTransDB].[dbo].[BIANALYSISSERVICESDATABASE];
INSERT INTO [$DestTransDB].[dbo].[BIANALYSISSERVICESDATABASE] ([ANALYSISSERVICESDATABASENAME],[BIANALYSISSERVER],[ISDEFAULT],[LASTDEPLOYEDDATETIME],[LASTDEPLOYEDDATETIMETZID],[PARTITIONS],[RECVERSION],[RECID])
VALUES ('Dynamics AX initial', 1,  1, '01-01-1900', 37001,  @PartitionId, 786535868, 5637144576);

UPDATE [$DestTransDB].[dbo].[SYSEMAILPARAMETERS] SET SMTPRELAYSERVERNAME='mail1.YOURDOMAIN.net';

--TRUNCATE TABLE [$DestTransDB].[dbo].[EPWEBSITEPARAMETERS];

--INSERT INTO [$DestTransDB].[dbo].[EPWEBSITEPARAMETERS] ([COMPANYID], [INTERNALURL] ,[TYPE] ,[SITEID] ,[ANONYMOUSACCESS] ,[EXTERNALURL] ,[ENABLEENCRYPTION] ,[ENCRYPTIONEXPIRATIONINTERVAL] ,[IMAGERESIZE] ,[IMAGESIZESMALL] , [IMAGESIZELARGE] ,[IMAGERATIO] ,[COMPANYINDEPENDENT] ,[SITEINSTALLATIONTYPE] ,[USEDEFAULTREPORTSERVER] ,[PARTITIONINDEPENDENT] ,[PARTITIONKEY] ,[RECVERSION] ,[RECID])
--VALUES ('CDS', 'http://ax-uat-ent-01:93/sites/DynamicsAx', 2, 'F21351AC-5C38-4121-92B3-1D5743CB2B15', 0, 'http://ax-uat-ent-01:93/sites/DynamicsAx', 1, 1, 1, 75, 150, 1, 1, 1, 1, 1, 'initial', 1, '5637144576');

TRUNCATE TABLE [$DestTransDB].[dbo].[AIFWEBSITES];
INSERT INTO [$DestTransDB].[dbo].[AIFWEBSITES] (VIRTUALDIRECTORYSHARE, NAME, URL, RECVERSION, RECID)
VALUES ('\\$(${ComputerName})\MicrosoftDynamicsAXAif60', '$(${ComputerName})-AXAifDEV-MicrosoftDynamicsAXAifDEV', 'http://$(${ComputerName}):92/MicrosoftDynamicsAXAiF60/', '999258545', '5637144576');

TRUNCATE TABLE [$DestTransDB].[dbo].[BICONFIGURATION];
INSERT INTO [$DestTransDB].[dbo].[BICONFIGURATION] ([PERFORMTRANSLATION] ,[CREATEROLES] ,[DATASOURCENAME] ,[CONNECTIONSTRING] ,[CREATECUBES] ,[ENABLELOGGING] ,[PROJECTNAME] ,[OPENGENERATEDPROJECT] ,[PROJECTFILENAME] ,
[CREATEDIMENSIONS] ,[UPDATECUBES] ,[OVERWRITETRANSLATIONS] ,[UPDATEDIMENSIONS]  ,[UPDATEMEASUREGROUPS] ,[UPDATEDIMENSIONUSAGE] ,[UPDATEHIERARCHIES] ,[UPDATEFRAMEWORKDIMENSIONUSAGE] ,[OPENUPDATEDPROJECT]  ,[DATASOURCETYPE]  ,
[ENABLECURRENCYCONVERSION]  ,[UPDATEDATEDIMENSIONS] ,[RECVERSION] ,[RECID])
VALUES ('1','0', 'Dynamics Database', 'Provider=SQLNCLI10.1;Data Source=$($ReportServerHost)\AXDEV;Integrated Security=SSPI;Initial Catalog=$($DestTransDB)', '0', '0', 'DynamicsAX', '0', 'C:\Users\USERNAME\AppData\Local\Temp\3\DynamicsAX\DynamicsAX.dwproj', '0', '0', '0', '0', '0', '0', '0', '0', '0', '2', '0', '0', '1290264436', '5637144576' )"


if (!($DestTransDB -eq $SourceTransDB))
{
Write-Host
Write-Host "Rename AX $target Database settings...."

Invoke-Sqlcmd -QueryTimeout 65535 -ServerInstance $SqlServerInstance -Query "

--Rename Restored DB's
GO
USE [$DestTransDB]
ALTER DATABASE [$DestTransDB] MODIFY FILE (NAME=N'$($SourceTransDB)', NEWNAME=N'$DestTransDB')
ALTER DATABASE [$DestTransDB] MODIFY FILE (NAME=N'$($SourceTransDB)_log', NEWNAME=N'$($DestTransDB)_log')
GO
USE [$($DestTransDB)_Model]
ALTER DATABASE [$($DestTransDB)_Model] MODIFY FILE (NAME=N'$($SourceTransDB)_Model', NEWNAME=N'$($DestTransDB)_Model')
ALTER DATABASE [$($DestTransDB)_Model] MODIFY FILE (NAME=N'$($SourceTransDB)_Model_log', NEWNAME=N'$($DestTransDB)_Model_log')"
}

if (!($Target -eq "DEV"))
{
Write-Host
Write-Host "Set AX $target Database to Simple Recovery Mode...."

Invoke-Sqlcmd -QueryTimeout 65535 -ServerInstance $SqlServerInstance -Query "

--Set DBs Simple Recovery Mode
GO
USE [$DestTransDB]
ALTER DATABASE [$DestTransDB] SET RECOVERY SIMPLE WITH NO_WAIT
GO
USE [$($DestTransDB)_Model]
ALTER DATABASE [$($DestTransDB)_Model] SET RECOVERY SIMPLE WITH NO_WAIT"

}

Write-Host
Write-Host "Shrink AX $target Databases...."

Invoke-Sqlcmd -QueryTimeout 65535 -ServerInstance $SqlServerInstance -Query "

--Shrink DB's and Files
GO
USE
[$DestTransDB]
DBCC SHRINKDATABASE(N'$DestTransDB' )
GO
USE
[$DestTransDB]
DBCC SHRINKFILE (N'$DestTransDB' , 0, TRUNCATEONLY)
GO
USE
[$DestTransDB]
DBCC SHRINKFILE (N'$($DestTransDB)_log' , 0, TRUNCATEONLY)
GO
USE
[$($DestTransDB)_Model]
DBCC SHRINKDATABASE(N'$($DestTransDB)_Model' )
GO
USE
[$($DestTransDB)_Model]
DBCC SHRINKFILE (N'$($DestTransDB)_model' , 0, TRUNCATEONLY)
GO
USE
[$($DestTransDB)_Model]
DBCC SHRINKFILE (N'$($DestTransDB)_model_log' , 0, TRUNCATEONLY) "

if ($? -ne "True") {
throw "An error occurred whilst recreating the AX Settings $_"
}
}
}

function Restore-Database
{

param(
[Parameter(Mandatory = $true)]
[string]$Source,
[Parameter(Mandatory = $true)]
[string]$Target,
[Parameter(Mandatory = $true)]
[string]$transDatabaseName,
[Parameter(Mandatory = $true)]
[string]$modelDatabaseName,
[Parameter(Mandatory = $true)]
[string]$StransDatabaseName,
[Parameter(Mandatory = $true)]
[string]$SmodelDatabaseName,
[Parameter(Mandatory = $true)]
[string]$dbServer,
[Parameter(Mandatory = $true)]
[string]$SdbServer,
[Parameter(Mandatory = $true)]
[string]$STransSource,
[Parameter(Mandatory = $true)]
[string]$SModelSource,
[Parameter(Mandatory = $true)]
[string]$DestData,
[Parameter(Mandatory = $true)]
[string]$DestLogs

)

Write-Host
Write-Host "Restoring $Source Transactional Database to $Target" -Fore Green -Back Red

Invoke-Sqlcmd -QueryTimeout 65535 -ServerInstance $dbServer -Query "
USE [master]
ALTER DATABASE [$transDatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE [$transDatabaseName] FROM  DISK = N'$STransSource' WITH  FILE = 1,  MOVE N'$StransDatabaseName' TO N'$($DestData)$($transDatabaseName).mdf',  MOVE N'$($StransDatabaseName)_log' TO N'$($DestLogs)$($transDatabaseName)_log.ldf',  NOUNLOAD,  REPLACE,  STATS = 5
ALTER DATABASE [$transDatabaseName] SET MULTI_USER

GO
"

Write-Host
Write-Host "Restoring $Source ModelStore Database to $Target" -Fore Green -Back Red

Invoke-Sqlcmd -QueryTimeout 65535 -ServerInstance $dbServer -Query "
USE [master]
ALTER DATABASE [$modelDatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE [$modelDatabaseName] FROM  DISK = N'$SModelSource' WITH  FILE = 1,  MOVE N'$SmodelDatabaseName' TO N'$($DestData)$($modelDatabaseName).mdf',  MOVE N'$($SmodelDatabaseName)_log' TO N'$($DestLogs)$($modelDatabaseName)_log.ldf',  NOUNLOAD,  REPLACE,  STATS = 5
ALTER DATABASE [$modelDatabaseName] SET MULTI_USER

GO
"

if ($? -ne "True") {
throw "An error occurred whilst restoring to $Target AX"
}

else
{ Write-Host
Write-Host "Both $Source Databases Restored successfully" -Fore Green -Back Red }

}

################################################################
# Pre-Config Environment VAR population
################################################################

if (!($RunHeadless.IsPresent))
{
# Step 1 : Check Admin Rights,
if (!(Test-AdministratorPrivileges))
{
Write-Warning -Message "###############################################"
Write-Warning -Message "# Please run this script with Admin Privileges"
Write-Warning -Message "###############################################"
Pause
return
}
#Menu to allow the user to choose which Environment to work with
Write-Host -Fore Green -Back Black
Write-Host -Fore Green -Back Black
Write-Host -Fore Green -Back Black
Write-Host -Fore Green -Back Black ******************************************************
Write-Host -Fore Green -Back Black ******************************************************
Write-Host -Fore Green -Back Black ********Please Choose the required Source*************
Write-Host -Fore Green -Back Black ******************************************************
Write-Host -Fore Green -Back Black *******This is the Env that you want to pull *********
Write-Host -Fore Green -Back Black ******************************************************
Write-Host -Fore Green -Back Black ******** CHDEV ***************************************
Write-Host -Fore Green -Back Black ******** DEV *****************************************
Write-Host -Fore Green -Back Black ******** UAT *****************************************
Write-Host -Fore Green -Back Black ******** GOLD ****************************************
Write-Host -Fore Green -Back Black ******** PROD ****************************************
Write-Host -Fore Green -Back Black ******************************************************
Write-Host -Fore Green -Back Black ******************************************************
Write-Host -Fore Green -Back Black
Write-Host -Fore Green -Back Black
Write-Host -Fore Green -Back Black
[ValidateSet("dev","uat","prod","gold","chdev",ignoreCase = $true)][Parameter(Mandatory = $false)] [string]$Source = Read-Host -Prompt 'Choose the Source Environment you want to pull'
$Source = $Source.ToUpper()
Write-Host -Fore Yellow "THE SOURCE IS $Source"
Pause
cls
Write-Host -Fore Green -Back Black
Write-Host -Fore Green -Back Black
Write-Host -Fore Green -Back Black
Write-Host -Fore Green -Back Black ******************************************************
Write-Host -Fore Green -Back Black ******************************************************
Write-Host -Fore Green -Back Black ********Please Choose the required Target*************
Write-Host -Fore Green -Back Black ******************************************************
Write-Host -Fore Green -Back Black *****This is the Env that you want to write to *******
Write-Host -Fore Green -Back Black ******************************************************
Write-Host -Fore Green -Back Black ******** LOCAL ***************************************
Write-Host -Fore Green -Back Black ******** DEV *****************************************
Write-Host -Fore Green -Back Black ******** CHDEV ***************************************
Write-Host -Fore Green -Back Black ******** GOLD ****************************************
Write-Host -Fore Green -Back Black ******************************************************
Write-Host -Fore Green -Back Black ******************************************************
Write-Host -Fore Green -Back Black
Write-Host -Fore Green -Back Black
Write-Host -Fore Green -Back Black
[ValidateSet("local","dev","chdev","gold",ignoreCase = $true)][Parameter(Mandatory = $false)] [string]$Target = Read-Host -Prompt 'Choose the Target Environment to ReCreate'
$target = $Target.ToUpper()
Write-Host -Fore Yellow "THE TARGET IS $Target"
Pause
cls
Write-Host -Fore White -Back Red
Write-Host -Fore White -Back Red
Write-Host -Fore White -Back Red
Write-Host -Fore White -Back Red ******************************************************
Write-Host -Fore White -Back Red ******************************************************
Write-Host -Fore White -Back Red ********** WARNING YOU ARE ABOUT TO WIPE *************
Write-Host -Fore White -Back Red "************ THE" -NoNewline
Write-Host -Fore Yellow -Back Red " $Target " -NoNewline
Write-Host -Fore White -Back Red "ENVIRONMENT *******************"
Write-Host -Fore White -Back Red ******************************************************
Write-Host -Fore White -Back Red ***********WARNING*****WARNING******WARNING **********
Write-Host -Fore White -Back Red ******************************************************
Write-Host -Fore White -Back Red ********** -NoNewline
Write-Host -Fore Yellow -Back Red " $Target " -NoNewline
Write-Host -Fore White -Back Red IS ABOUT TO BE RECREATED *************
Write-Host -Fore White -Back Red ********** FROM THE -NoNewline
Write-Host -Fore Yellow -Back Red " $Source " -NoNewline
Write-Host -Fore White -Back Red ENVIRONMENT ***************
Write-Host -Fore White -Back Red ******************************************************
Write-Host -Fore White -Back Red ******************************************************
Write-Host -Fore White -Back Red
Write-Host -Fore White -Back Red
Write-Host -Fore White -Back Red
Pause
}

################################################################
# Configuration
################################################################
$Source = $Source.ToUpper()
$target = $Target.ToUpper()

$environments = @{
"LOCAL" = @{
"aosServers" = @( "$ENV:Computername")
"aosServiceUser" = "YOURDOMAIN\AXDEVAOS"
"enterprisePortalUrl" = ""
"reportingServers" = "$ENV:Computername"
"databaseServer" = "$ENV:Computername\AXDEV"
"modelDatabaseName" = "MicrosoftDynamicsAX_Model"
"transDatabaseName" = "MicrosoftDynamicsAX"
"configuration" = "build-client.axc"
"serverBinDir" = "C:\Program Files\Microsoft Dynamics AX\60\Server\MicrosoftDynamicsAX\bin"
"DestData" = "C:\Program Files\Microsoft SQL Server\MSSQL12.AXDEV\MSSQL\DATA\"
"DestLogs" = "C:\Program Files\Microsoft SQL Server\MSSQL12.AXDEV\MSSQL\DATA\"
"DestBackup" = "C:\Source\"
}
"DEV" = @{
"aosServers" = @( "AX-DEV-AOS-01")
"aosServiceUser" = "YOURDOMAIN\AXAOSACC"
"enterprisePortalUrl" = "http://ax-uat-ent-01:93/sites/DynamicsAx"
"reportingServers" = "AX-DEV-RS-01"
"databaseServer" = @( "AX-UAT-SQL-01\SQL_AX_DEV1")
"modelDatabaseName" = "AX63_CDS_DEV_model"
"transDatabaseName" = "AX63_CDS_DEV"
"configuration" = "DEV-client.axc"
"serverBinDir" = "C:\Program Files\Microsoft Dynamics AX\60\Server\AX63_CDS_DEV\bin"
"TransSource" = "\\AX-UAT-SQL-01\DEV-Backup$\AX63_CDS_DEV.bak"
"ModelSource" = "\\AX-UAT-SQL-01\DEV-Backup$\AX63_CDS_DEV_model.bak"
"DestData" = "D:\Microsoft SQL Server\MSSQL12.SQL_AX_DEV1\MSSQL\DATA\"
"DestLogs" = "F:\Microsoft SQL Server\MSSQL12.SQL_AX_DEV1\MSSQL\DATA\"
"DestBackup" = "E:\DEV-Backup\"
}
"UAT" = @{
"aosServers" = @( "AX-UAT-AOS-01","AX-UAT-BAT-01")
"aosServiceUser" = "YOURDOMAIN\AXAOSACC"
"enterprisePortalUrl" = "http://ax-uat-ent-01:83/sites/DynamicsAx"
"reportingServers" = "AX-UAT-RS-01"
"databaseServer" = @( "AX-UAT-SQL-01\SQL_AX_UAT1")
"modelDatabaseName" = "AX63_CDS_UAT_model"
"transDatabaseName" = "AX63_CDS_UAT"
"configuration" = "UAT-client.axc"
"serverBinDir" = "C:\Program Files\Microsoft Dynamics AX\60\Server\AX63_CDS_UAT\bin"
"TransSource" = "\\AX-UAT-SQL-01\UAT-Backup$\AX63_CDS_UAT.bak"
"ModelSource" = "\\AX-UAT-SQL-01\UAT-Backup$\AX63_CDS_UAT_model.bak"
"DestData" = "D:\Microsoft SQL Server\MSSQL12.SQL_AX_UAT1\MSSQL\DATA\"
"DestLogs" = "F:\Microsoft SQL Server\MSSQL12.SQL_AX_UAT1\MSSQL\DATA\"
"DestBackup" = "E:\UAT-Backup\"
}
"PROD" = @{
"aosServers" = @( "AX-AOS-01","AX-AOS-02","AX-AOS-03","AX-AOS-04","AX-AOS-BAT-01")
"aosServiceUser" = "YOURDOMAIN\AXAOSACC"
"enterprisePortalUrl" = "http://ax-ent-01:83/sites/DynamicsAx/"
"reportingServers" = "AX-RS-01"
"databaseServer" = @( "AX-SQL-00\SQL_AX1")
"modelDatabaseName" = "AX63_CDS_PROD_model"
"transDatabaseName" = "AX63_CDS_PROD"
"configuration" = "production.axc"
"serverBinDir" = "C:\Program Files\Microsoft Dynamics AX\60\Server\AX63_CDS_PROD\bin"
"TransSource" = "\\AX-SQL-01\PROD-Backup$\AX63_CDS_PROD.bak"
"ModelSource" = "\\AX-SQL-01\PROD-Backup$\AX63_CDS_PROD_model.bak"
"DestData" = "D:\MSSQL12.SQL_AX1.Data\"
"DestLogs" = "F:\MSSQL12.SQL_AX1.Logs\"
"DestBackup" = "E:\PROD-Backup\"
}
"GOLD" = @{
"aosServers" = @( "CH-WV-AX-GOLD-T")
"aosServiceUser" = "YOURDOMAIN\AXDEVAOS"
"enterprisePortalUrl" = "http://ax-ent-01:83/sites/DynamicsAx/"
"reportingServers" = "CH-WV-AX-GOLD-T"
"databaseServer" = @( "CH-WV-AX-GOLD-T\AXDEV")
"modelDatabaseName" = "MicrosoftDynamicsAX_Model"
"transDatabaseName" = "MicrosoftDynamicsAX"
"configuration" = "AXGOLD.axc"
"serverBinDir" = "C:\Program Files\Microsoft Dynamics AX\60\Server\MicrosoftDynamicsAX\bin"
"TransSource" = "\\CH-WV-AX-GOLD-T\GOLD-Backup$\MicrosoftDynamicsAX.bak"
"ModelSource" = "\\CH-WV-AX-GOLD-T\GOLD-Backup$\MicrosoftDynamicsAX_Model.bak"
"DestData" = "E:\MSSQL12.AXDEV\MSSQL\DATA\"
"DestLogs" = "F:\MSSQL12.AXDEV\MSSQL\DATA\"
"DestBackup" = "C:\GOLD-Backup\"
}
"CHDEV" = @{
"aosServers" = @( "CH-AXDEV-AOS-01")
"aosServiceUser" = "YOURDOMAIN\AXDEVAOS"
"enterprisePortalUrl" = "http://ax-ent-01:83/sites/DynamicsAx/"
"reportingServers" = "CH-AXDEV-RS-01"
"databaseServer" = @( "CH-WV-SQL-01\AXDEV")
"modelDatabaseName" = "MicrosoftDynamicsAX_Model"
"transDatabaseName" = "MicrosoftDynamicsAX"
"configuration" = "AX_CH_DEV.axc"
"serverBinDir" = "C:\Program Files\Microsoft Dynamics AX\60\Server\MicrosoftDynamicsAX\bin"
"TransSource" = "\\CH-WV-SQL-01\CHDEV$\MicrosoftDynamicsAX.bak"
"ModelSource" = "\\CH-WV-SQL-01\CHDEV$\MicrosoftDynamicsAX_Model.bak"
"DestData" = "E:\MSSQL12.AXDEV\MSSQL\DATA\"
"DestLogs" = "F:\MSSQL12.AXDEV\MSSQL\DATA\"
"DestBackup" = "C:\Source\CHDEV\"
}
};

# Destination Configuration options
$dest = $environments[$Target.ToUpperInvariant()]
$aosInstances = $dest.aosServers
[string]$dbServer = $dest.databaseServer
$aosUser = $dest.aosServiceUser
$reportingServers = $dest.reportingServers
$enterpriseUrl = $dest.enterprisePortalUrl
$modelDatabaseName = $dest.modelDatabaseName
$transDatabaseName = $dest.transDatabaseName
$serverBinDir = $dest.serverBinDir
$DestData = $dest.DestData
$DestLogs = $dest.DestLogs
$DestBackup = $dest.DestBackup

# Source Configuration options
$src = $environments[$Source.ToUpperInvariant()]
$SaosInstances = $src.aosServers
[string]$SdbServer = $src.databaseServer
$SaosUser = $src.aosServiceUser
$SreportingServers = $src.reportingServers
$SenterpriseUrl = $src.enterprisePortalUrl
$SmodelDatabaseName = $src.modelDatabaseName
$StransDatabaseName = $src.transDatabaseName
$SserverBinDir = $src.serverBinDir
$STransSource = $src.TransSource
$SModelSource = $src.ModelSource
$SDestBackup = $src.DestBackup

# Common Configuration options
$axServerInstallPath = "C:\Program Files\Microsoft Dynamics AX\60\Server\$transDatabaseName\bin"

##################################################################
# Importing Required Modules
##################################################################

Import-Module sqlps –DisableNameChecking
Import-Module "C:\Program Files\Microsoft Dynamics AX\60\ManagementUtilities\Modules\AXUtilLib.Powershell\AXUtilLib.PowerShell.dll"
Import-Module "C:\Program Files\Microsoft Dynamics AX\60\ManagementUtilities\Modules\Microsoft.Dynamics.AX.Framework.Management\Microsoft.Dynamics.AX.Framework.Management.dll"
."C:\Program Files\Microsoft Dynamics AX\60\ManagementUtilities\Microsoft.Dynamics.ManagementUtilities.ps1"

##################################################################
# Main Application Starts Running Here
##################################################################
cls
Write-Host "*************************************************************************"
Write-Host " Recreate $Target AX from $Source AX"
Write-Host "*************************************************************************"
Write-Host
Write-Host "*************************************************************************"
Write-Host "Environment Recreation Settings"
Write-Host "*************************************************************************"
Write-Host "TARGET                : $Target"
Write-Host "DB Server             : $dbServer"
Write-Host "Model DB Name         : $modelDatabaseName"
Write-Host "Trans DB Name         : $transDatabaseName"
Write-Host "SOURCE                : $Source"
Write-Host "DB Server             : $SdbServer"
Write-Host "Model DB Name         : $SmodelDatabaseName"
Write-Host "Trans DB Name         : $StransDatabaseName"
Write-Host "Trans Source DB       : $STransSource"
Write-Host "Model Source DB       : $SModelSource"
Write-Host "*************************************************************************"
try
{
# Step 1 : Check Admin Rights,
if (!(Test-AdministratorPrivileges))
{
Write-Host -ForegroundColor Red "Please run this script with admin privileges"
return
}

Push-Location

# Step 2 : Take new $Source AX Backup
Write-Host "Starting $Source AX Transactional Database Backup"
Backup-SqlDatabase -CompressionOption On -ServerInstance $SdbServer -Database $StransDatabaseName -BackupFile "$($SDestBackup)$($STransDatabaseName).bak" -Initialize
Write-Host "Starting $Source AX ModelStore Database Backup"
Backup-SqlDatabase -CompressionOption On -ServerInstance $SdbServer -Database $SModelDatabaseName -BackupFile "$($SDestBackup)$($SModelDatabaseName).bak" -Initialize

# Step 3 : Stop AOS
Write-Host "Stopping AOS Service on Destination!"
Stop-Service -Name 'AOS60$01'

# Step 4  : Backup $Target AX Databases
Write-Host "Starting $Target AX Transactional Database Backup"
Backup-SqlDatabase -ServerInstance $dbServer -Database $transDatabaseName -BackupFile "$($DestBackup)$($TransDatabaseName).bak" -CompressionOption On -Initialize
Write-Host "Starting $Target AX ModelStore Database Backup"
Backup-SqlDatabase -ServerInstance $dbServer -Database $ModelDatabaseName -BackupFile "$($DestBackup)$($ModelDatabaseName).bak" -CompressionOption On -Initialize

# Step 5: Restore(!)
Write-Host "Restoring $Source AX Database to $Target AX!!!!"
Restore-Database -Source $Source -Target $Target -TransDatabaseName $transDatabaseName -ModelDatabaseName $modelDatabaseName -StransDatabaseName $StransDatabaseName -SModelDatabaseName $SmodelDatabaseName -dbServer $dbServer -SdbServer $SdbServer -SModelSource $SModelSource -STransSource $STransSource -DestData $DestData -DestLogs $DestLogs

# Step 6: Reconfigure AX DEV settings
Write-Host "Reconfiguring AX $Target Settings"
Reconfigure-AxConfiguration -ComputerName $ENV:Computername -SqlServerInstance $dbServer -DestTransDB $transDatabaseName -SourceTransDB $StransDatabaseName -ReportServerHost $reportingServers

# Step 7 : Blow away XPIL etc...
Write-Host "Clearing $Target XPPIL cache"
if (Test-Path -Path "$axServerInstallPath\XPPIL") {
Remove-Item -Path "$axServerInstallPath\XPPIL" -Recurse
}

# Step 8 : Restart AOS on Destination
Write-Host "Starting AOS Service on Destination!"
Start-Service -Name 'AOS60$01'

# Step 8.1 : Couple of minute timeout to allow the service to fully start and the next steps complete successfully
Write-Host "Waiting 2 Minutes for Service to Fully Start up"
Start-Sleep -seconds 120


# Step 9 : Check Business Connector Install and Install if not present.
if (!(Test-Path "C:\Program Files\Microsoft Dynamics AX\60\BusinessConnector\Bin\Microsoft.Dynamics.BusinessConnectorNet.dll"))
{ Write-Host "Installing MS Dynamics AX 2012 R3 Business Connector - Please Wait....."
Start-Process "\\CH-WV-FS-01\AXDev$\Source\AX_2012_R3_Installer\setup.exe" ParmFile="\\CH-WV-FS-01\AXDev$\Source\Scripts\SQLImageComplete-1\AXBusinessConnector.txt" -NoNewWindow -Wait
}

# Step 10 : If LOCAL or GOLD Desktop Devs are Admin
if (($Target -eq "LOCAL" -or $Target -eq "GOLD") -and (!($Source -eq "GOLD" -or $Source -eq "CHDEV")))
{ Write-Host "Recreating Desktop DEV Sys Admins!"
New-AXUser -AccountType WindowsGroup -AXUserId DDEVADM -UserName "AX DesktopDEV - SysAdmins" -UserDomain YOURDOMAIN -Company YOURCOMPANY
Add-AXSecurityRoleMember -AOTName "-SYSADMIN-" -AXUserId DDEVADM
}

# Step 11 : If DEV or CHDEV - Needs DEV Admin Group
if ($Target -eq "DEV" -or $Target -eq "CHDEV")
{ Write-Host "Enabling DEV RDS Access"
New-AXUser -AccountType WindowsGroup -AXUserId ADAXDEV -UserName "AX RDS DEV Access" -UserDomain YOURDOMAIN -Company YOURCOMPANY
Add-AXSecurityRoleMember -AOTName "SystemUser" -AXUserId ADAXDEV
Add-AXSecurityRoleMember -AOTName "HCMEmployee" -AXUserId ADAXDEV
Write-Host "Recreating DEV Sys Admins!"
New-AXUser -AccountType WindowsGroup -AXUserId DEVADM -UserName "AX DEV - SysAdmins" -UserDomain YOURDOMAIN -Company YOURCOMPANY
Add-AXSecurityRoleMember -AOTName "-SYSADMIN-" -AXUserId DEVADM
}

if (!($RunHeadless.IsPresent))
{ Pause }
}
catch
{
$exception = $_.Exception | Format-List -Force | Out-String
Write-Host $exception
if (!($RunHeadless.IsPresent))
{ Pause }
}
Advertisements
Recreating AX Environments / Moving Databases

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s