+44 07967 557183

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.

  • Check for Local Admin Rights_
  • Take new $Source AX Backup_
  • Stop AOS on Target Environment_
  • Backup $Target AX Databases_
  • Restore $Source DB to $Target DB_
  • Reconfigure $Target settings (i.e change server names and service accounts etc)_
  • Delete XppIL Cache on $Target_
  • Restart AOS on $Target_
  • Couple of minute time-out to allow the service to fully start and the next steps complete successfully _
  • Check Business Connector Install and Install if not present._
  • If LOCAL or GOLD Desktop Devs are Admin and access is recreated_
  • 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.

  1param(
  2  [ValidateSet("local","dev","chdev","gold",ignoreCase = $true)]
  3  [Parameter(Mandatory = $false)]
  4  [string]$Target,
  5  [ValidateSet("dev","uat","prod","gold","chdev",ignoreCase = $true)]
  6  [Parameter(Mandatory = $false)]
  7  [string]$Source,
  8  [switch]$RunHeadless
  9)
 10
 11################################################################
 12# Recreate_AX_Env.ps1
 13#
 14# A powershell script for restoring and reconfiguring AX backups
 15# to any AX Environment.
 16#
 17################################################################
 18# Functions / Methods
 19################################################################
 20
 21function Test-AdministratorPrivileges
 22{
 23  $identity = [System.Security.Principal.WindowsIdentity]::GetCurrent()
 24  $principal = New-Object System.Security.Principal.WindowsPrincipal ($identity)
 25  $principal.IsInRole([System.Security.Principal.WindowsBuiltInRole]::Administrator)
 26}
 27
 28function Reconfigure-AxConfiguration
 29{
 30
 31  param(
 32    [Parameter(Mandatory = $true)]
 33    [string]$ComputerName,
 34    [Parameter(Mandatory = $true)]
 35    [string]$SqlServerInstance,
 36    [Parameter(Mandatory = $true)]
 37    [string]$DestTransDB,
 38    [Parameter(Mandatory = $true)]
 39    [string]$SourceTransDB,
 40    [Parameter(Mandatory = $true)]
 41    [string]$ReportServerHost,
 42    [Parameter(Mandatory = $true)]
 43    [string]$ServerURL,
 44    [Parameter(Mandatory = $true)]
 45    [string] $ManagerURL,
 46    [Parameter(Mandatory = $true)]
 47    [string] $RSInstance
 48
 49  )
 50
 51  process
 52  {
 53    Write-Host
 54    Write-Host "Updating AX $target settings...."
 55
 56    Invoke-Sqlcmd -QueryTimeout 65535 -ServerInstance $SqlServerInstance -Query "
 57        DECLARE @PartitionId bigint;
 58
 59        -- Get ID of the partition..
 60        SELECT @PartitionId = RECID FROM [$DestTransDB].[dbo].[PARTITIONS] WHERE PARTITIONKEY='initial';
 61
 62        -- Drop Server GUID so that the Cache refreshes etc
 63        UPDATE [$DestTransDB].[dbo].[SysSQMSettings] Set GlobalGUID = '{00000000-0000-0000-0000-000000000000}'
 64
 65        -- Reconfigure UAT to local..
 66        UPDATE [$DestTransDB].[dbo].[SYSGLOBALCONFIGURATION] SET VALUE='http://${ComputerName}:80/DynamicsAX6HelpServer/HelpService.svc' WHERE NAME='HelpServerLocation';
 67        
 68        TRUNCATE TABLE [$DestTransDB].[dbo].[SYSSERVERCONFIG];
 69        TRUNCATE TABLE [$DestTransDB].[dbo].[SYSSERVERSESSIONS];
 70        TRUNCATE TABLE [$DestTransDB].[dbo].[SYSCLIENTSESSIONS];
 71
 72        TRUNCATE TABLE [$DestTransDB].[dbo].[SRSSERVERS];
 73        INSERT INTO [$DestTransDB].[dbo].[SRSSERVERS] (SERVERID, ISDEFAULTREPORTMODELSERVER, SERVERURL, ISDEFAULTREPORTLIBRARYSERVER, AXAPTAREPORTFOLDER, REPORTMANAGERURL, SERVERINSTANCE, AOSID, CONFIGURATIONID, ISSHAREPOINTINTEGRATED, RECVERSION, RECID)
 74        VALUES ('$ReportServerHost',  0, 'http://$($ReportServerHost)/$($ServerURL)', 1, 'DynamicsAx', 'http://$($ReportServerHost)/$($ManagerURL)', '$RSInstance', '01@$($aosInstances)',  '01@$($aosInstances)',  0, 1,  1 )
 75
 76        TRUNCATE TABLE  [$DestTransDB].[dbo].[BIANALYSISSERVER];
 77        INSERT INTO [$DestTransDB].[dbo].[BIANALYSISSERVER] (SERVERNAME, DESCRIPTION, ISVALID, ISDEFAULT, DEFAULTDATABASENAME, PARTITIONS, RECVERSION, RECID)
 78        VALUES ('$ReportServerHost', 'Dynamics AX analysis server', 1, 1, 'Dynamics AX initial', @PartitionId, 1, 1);
 79
 80        TRUNCATE TABLE [$DestTransDB].[dbo].[BIANALYSISSERVICESDATABASE];
 81        INSERT INTO [$DestTransDB].[dbo].[BIANALYSISSERVICESDATABASE] ([ANALYSISSERVICESDATABASENAME],[BIANALYSISSERVER],[ISDEFAULT],[LASTDEPLOYEDDATETIME],[LASTDEPLOYEDDATETIMETZID],[PARTITIONS],[RECVERSION],[RECID])
 82        VALUES ('Dynamics AX initial', 1,  1, '01-01-1900', 37001,  @PartitionId, 786535868, 5637144576);
 83
 84        UPDATE [$DestTransDB].[dbo].[SYSEMAILPARAMETERS] SET SMTPRELAYSERVERNAME='mail1.connect-distribution.net';
 85        
 86        --TRUNCATE TABLE [$DestTransDB].[dbo].[EPWEBSITEPARAMETERS];
 87
 88        --INSERT INTO [$DestTransDB].[dbo].[EPWEBSITEPARAMETERS] ([COMPANYID], [INTERNALURL] ,[TYPE] ,[SITEID] ,[ANONYMOUSACCESS] ,[EXTERNALURL] ,[ENABLEENCRYPTION] ,[ENCRYPTIONEXPIRATIONINTERVAL] ,[IMAGERESIZE] ,[IMAGESIZESMALL] , [IMAGESIZELARGE] ,[IMAGERATIO] ,[COMPANYINDEPENDENT] ,[SITEINSTALLATIONTYPE] ,[USEDEFAULTREPORTSERVER] ,[PARTITIONINDEPENDENT] ,[PARTITIONKEY] ,[RECVERSION] ,[RECID])
 89        --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');
 90
 91        --TRUNCATE TABLE [$DestTransDB].[dbo].[AIFWEBSITES];
 92        --INSERT INTO [$DestTransDB].[dbo].[AIFWEBSITES] (VIRTUALDIRECTORYSHARE, NAME, URL, RECVERSION, RECID)
 93        --VALUES ('\\$(${ComputerName})\MicrosoftDynamicsAXAif60', '$(${ComputerName})-AXAifDEV-MicrosoftDynamicsAXAifDEV', 'http://$(${ComputerName}):92/MicrosoftDynamicsAXAiF60/', '999258545', '5637144576');
 94
 95        TRUNCATE TABLE [$DestTransDB].[dbo].[BICONFIGURATION];
 96        INSERT INTO [$DestTransDB].[dbo].[BICONFIGURATION] ([PERFORMTRANSLATION] ,[CREATEROLES] ,[DATASOURCENAME] ,[CONNECTIONSTRING] ,[CREATECUBES] ,[ENABLELOGGING] ,[PROJECTNAME] ,[OPENGENERATEDPROJECT] ,[PROJECTFILENAME] ,
 97        [CREATEDIMENSIONS] ,[UPDATECUBES] ,[OVERWRITETRANSLATIONS] ,[UPDATEDIMENSIONS]  ,[UPDATEMEASUREGROUPS] ,[UPDATEDIMENSIONUSAGE] ,[UPDATEHIERARCHIES] ,[UPDATEFRAMEWORKDIMENSIONUSAGE] ,[OPENUPDATEDPROJECT]  ,[DATASOURCETYPE]  ,
 98        [ENABLECURRENCYCONVERSION]  ,[UPDATEDATEDIMENSIONS] ,[RECVERSION] ,[RECID])
 99        VALUES ('1','0', 'Dynamics Database', 'Provider=SQLNCLI10.1;Data Source=$($SqlServerInstance);Integrated Security=SSPI;Initial Catalog=$($DestTransDB)', '0', '0', 'DynamicsAX', '0', 'C:\Users\K3ADMIN\AppData\Local\Temp\3\DynamicsAX\DynamicsAX.dwproj', '0', '0', '0', '0', '0', '0', '0', '0', '0', '2', '0',	'0', '1290264436', '5637144576' )
100
101         --K3 Fix for Slow AX Client Startup ???
102        USE [$($DestTransDB)_Model];
103        IF NOT EXISTS(SELECT name FROM sys.indexes WHERE name = 'ParentIDX' )
104        BEGIN
105        CREATE NONCLUSTERED INDEX [ParentIDX] ON [$($DestTransDB)_Model].[dbo].[ModelElement] ([ParentId])
106        END
107        GO                                            "
108
109
110    if (!($DestTransDB -eq $SourceTransDB))
111    {
112      Write-Host
113      Write-Host "Rename AX $target Database settings...."
114
115      Invoke-Sqlcmd -QueryTimeout 65535 -ServerInstance $SqlServerInstance -Query "
116
117         --Rename Restored DB's 
118          GO
119		  USE [$DestTransDB]
120          ALTER DATABASE [$DestTransDB] MODIFY FILE (NAME=N'$($SourceTransDB)', NEWNAME=N'$DestTransDB')
121          ALTER DATABASE [$DestTransDB] MODIFY FILE (NAME=N'$($SourceTransDB)_log', NEWNAME=N'$($DestTransDB)_log')
122          GO
123		  USE [$($DestTransDB)_Model]
124          ALTER DATABASE [$($DestTransDB)_Model] MODIFY FILE (NAME=N'$($SourceTransDB)_Model', NEWNAME=N'$($DestTransDB)_Model')
125          ALTER DATABASE [$($DestTransDB)_Model] MODIFY FILE (NAME=N'$($SourceTransDB)_Model_log', NEWNAME=N'$($DestTransDB)_Model_log')"
126    }
127
128    if (!($Target -eq "DEV"))
129    {
130      Write-Host
131      Write-Host "Set AX $target Database to Simple Recovery Mode...."
132
133      Invoke-Sqlcmd -QueryTimeout 65535 -ServerInstance $SqlServerInstance -Query "
134
135         --Set DBs Simple Recovery Mode
136          GO
137		  USE [$DestTransDB]
138          ALTER DATABASE [$DestTransDB] SET RECOVERY SIMPLE WITH NO_WAIT
139          GO
140		  USE [$($DestTransDB)_Model]
141          ALTER DATABASE [$($DestTransDB)_Model] SET RECOVERY SIMPLE WITH NO_WAIT"
142
143    }
144
145    Write-Host
146    Write-Host "Shrink AX $target Databases...."
147
148    Invoke-Sqlcmd -QueryTimeout 65535 -ServerInstance $SqlServerInstance -Query "
149
150          --Shrink DB's and Files
151		  GO
152		  USE
153		  [$DestTransDB]
154          DBCC SHRINKDATABASE(N'$DestTransDB' )
155		  GO
156		  USE
157		  [$DestTransDB]
158          DBCC SHRINKFILE (N'$DestTransDB' , 0, TRUNCATEONLY)
159		  GO
160		  USE
161		  [$DestTransDB]
162          DBCC SHRINKFILE (N'$($DestTransDB)_log' , 0, TRUNCATEONLY)
163		  GO
164		  USE
165		  [$($DestTransDB)_Model]
166          DBCC SHRINKDATABASE(N'$($DestTransDB)_Model' )
167		  GO
168		  USE
169		  [$($DestTransDB)_Model]
170          DBCC SHRINKFILE (N'$($DestTransDB)_model' , 0, TRUNCATEONLY)
171		  GO
172		  USE
173		  [$($DestTransDB)_Model]
174          DBCC SHRINKFILE (N'$($DestTransDB)_model_log' , 0, TRUNCATEONLY) "
175
176    if ($? -ne "True") {
177      throw "An error occurred whilst recreating the AX Settings $_"
178    }
179  }
180}
181
182function Restore-Database
183{
184
185  param(
186    [Parameter(Mandatory = $true)]
187    [string]$Source,
188    [Parameter(Mandatory = $true)]
189    [string]$Target,
190    [Parameter(Mandatory = $true)]
191    [string]$transDatabaseName,
192    [Parameter(Mandatory = $true)]
193    [string]$modelDatabaseName,
194    [Parameter(Mandatory = $true)]
195    [string]$StransDatabaseName,
196    [Parameter(Mandatory = $true)]
197    [string]$SmodelDatabaseName,
198    [Parameter(Mandatory = $true)]
199    [string]$dbServer,
200    [Parameter(Mandatory = $true)]
201    [string]$SdbServer,
202    [Parameter(Mandatory = $true)]
203    [string]$STransSource,
204    [Parameter(Mandatory = $true)]
205    [string]$SModelSource,
206    [Parameter(Mandatory = $true)]
207    [string]$DestData,
208    [Parameter(Mandatory = $true)]
209    [string]$DestLogs
210
211  )
212
213  Write-Host
214  Write-Host "Restoring $Source Transactional Database to $Target" -Fore Green -Back Red
215
216  Invoke-Sqlcmd -QueryTimeout 65535 -ServerInstance $dbServer -Query "
217USE [master]
218ALTER DATABASE [$transDatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
219RESTORE 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
220ALTER DATABASE [$transDatabaseName] SET MULTI_USER
221
222GO
223"
224
225  Write-Host
226  Write-Host "Restoring $Source ModelStore Database to $Target" -Fore Green -Back Red
227
228  Invoke-Sqlcmd -QueryTimeout 65535 -ServerInstance $dbServer -Query " 
229USE [master]
230ALTER DATABASE [$modelDatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
231RESTORE 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
232ALTER DATABASE [$modelDatabaseName] SET MULTI_USER
233
234GO
235"
236
237  if ($? -ne "True") {
238    throw "An error occurred whilst restoring to $Target AX"
239  }
240
241  else
242  { Write-Host
243    Write-Host "Both $Source Databases Restored successfully" -Fore Green -Back Red }
244
245}
246
247################################################################
248# Pre-Config Environment VAR population
249################################################################
250
251if (!($RunHeadless.IsPresent))
252{
253  # Step 1 : Check Admin Rights,
254  if (!(Test-AdministratorPrivileges))
255  {
256    Write-Warning -Message "###############################################"
257    Write-Warning -Message "# Please run this script with Admin Privileges"
258    Write-Warning -Message "###############################################"
259    Pause
260    return
261  }
262  #Menu to allow the user to choose which Environment to work with
263  Write-Host -Fore Green -Back Black
264  Write-Host -Fore Green -Back Black
265  Write-Host -Fore Green -Back Black
266  Write-Host -Fore Green -Back Black ******************************************************
267  Write-Host -Fore Green -Back Black ******************************************************
268  Write-Host -Fore Green -Back Black ********Please Choose the required Source*************
269  Write-Host -Fore Green -Back Black ******************************************************
270  Write-Host -Fore Green -Back Black *******This is the Env that you want to pull *********
271  Write-Host -Fore Green -Back Black ******************************************************
272  Write-Host -Fore Green -Back Black ******** CHDEV ***************************************
273  Write-Host -Fore Green -Back Black ******** DEV *****************************************
274  Write-Host -Fore Green -Back Black ******** UAT *****************************************
275  Write-Host -Fore Green -Back Black ******** GOLD ****************************************
276  Write-Host -Fore Green -Back Black ******** PROD ****************************************
277  Write-Host -Fore Green -Back Black ******************************************************
278  Write-Host -Fore Green -Back Black ******************************************************
279  Write-Host -Fore Green -Back Black
280  Write-Host -Fore Green -Back Black
281  Write-Host -Fore Green -Back Black
282  [ValidateSet("dev","uat","prod","gold","chdev",ignoreCase = $true)][Parameter(Mandatory = $false)] [string]$Source = Read-Host -Prompt 'Choose the Source Environment you want to pull'
283  $Source = $Source.ToUpper()
284  Write-Host -Fore Yellow "THE SOURCE IS $Source"
285  Pause
286  cls
287  Write-Host -Fore Green -Back Black
288  Write-Host -Fore Green -Back Black
289  Write-Host -Fore Green -Back Black
290  Write-Host -Fore Green -Back Black ******************************************************
291  Write-Host -Fore Green -Back Black ******************************************************
292  Write-Host -Fore Green -Back Black ********Please Choose the required Target*************
293  Write-Host -Fore Green -Back Black ******************************************************
294  Write-Host -Fore Green -Back Black *****This is the Env that you want to write to *******
295  Write-Host -Fore Green -Back Black ******************************************************
296  Write-Host -Fore Green -Back Black ******** LOCAL ***************************************
297  Write-Host -Fore Green -Back Black ******** DEV *****************************************
298  Write-Host -Fore Green -Back Black ******** CHDEV ***************************************
299  Write-Host -Fore Green -Back Black ******** GOLD ****************************************
300  Write-Host -Fore Green -Back Black ******************************************************
301  Write-Host -Fore Green -Back Black ******************************************************
302  Write-Host -Fore Green -Back Black
303  Write-Host -Fore Green -Back Black
304  Write-Host -Fore Green -Back Black
305  [ValidateSet("local","dev","chdev","gold",ignoreCase = $true)][Parameter(Mandatory = $false)] [string]$Target = Read-Host -Prompt 'Choose the Target Environment to ReCreate'
306  $target = $Target.ToUpper()
307  Write-Host -Fore Yellow "THE TARGET IS $Target"
308  Pause
309  cls
310  Write-Host -Fore White -Back Red
311  Write-Host -Fore White -Back Red
312  Write-Host -Fore White -Back Red
313  Write-Host -Fore White -Back Red ******************************************************
314  Write-Host -Fore White -Back Red ******************************************************
315  Write-Host -Fore White -Back Red ********** WARNING YOU ARE ABOUT TO WIPE *************
316  Write-Host -Fore White -Back Red "************ THE" -NoNewline
317  Write-Host -Fore Yellow -Back Red " $Target " -NoNewline
318  Write-Host -Fore White -Back Red "ENVIRONMENT *******************"
319  Write-Host -Fore White -Back Red ******************************************************
320  Write-Host -Fore White -Back Red ***********WARNING*****WARNING******WARNING **********
321  Write-Host -Fore White -Back Red ******************************************************
322  Write-Host -Fore White -Back Red ********** -NoNewline
323  Write-Host -Fore Yellow -Back Red " $Target " -NoNewline
324  Write-Host -Fore White -Back Red IS ABOUT TO BE RECREATED *************
325  Write-Host -Fore White -Back Red ********** FROM THE -NoNewline
326  Write-Host -Fore Yellow -Back Red " $Source " -NoNewline
327  Write-Host -Fore White -Back Red ENVIRONMENT ***************
328  Write-Host -Fore White -Back Red ******************************************************
329  Write-Host -Fore White -Back Red ******************************************************
330  Write-Host -Fore White -Back Red
331  Write-Host -Fore White -Back Red
332  Write-Host -Fore White -Back Red
333  Pause
334}
335
336################################################################
337# Configuration
338################################################################
339$Source = $Source.ToUpper()
340$target = $Target.ToUpper()
341
342$environments = @{
343  "LOCAL" = @{
344    "aosServers" = @( "$ENV:Computername")
345    "aosServiceUser" = "CONNECT\AXDEVAOS"
346    "enterprisePortalUrl" = ""
347    "reportingServers" = "$ENV:Computername"
348    "ServerURL" = "ReportServer"
349    "ManagerURL" = "Reports"
350    "RSInstance" = "MSSQLSERVER"
351    "databaseServer" = "$ENV:Computername\AXDEV"
352    "modelDatabaseName" = "MicrosoftDynamicsAX_Model"
353    "transDatabaseName" = "MicrosoftDynamicsAX"
354    "configuration" = "build-client.axc"
355    "serverBinDir" = "C:\Program Files\Microsoft Dynamics AX\60\Server\MicrosoftDynamicsAX\bin"
356    "DestData" = "C:\Program Files\Microsoft SQL Server\MSSQL12.AXDEV\MSSQL\DATA\"
357    "DestLogs" = "C:\Program Files\Microsoft SQL Server\MSSQL12.AXDEV\MSSQL\DATA\"
358    "DestBackup" = "C:\Source\"
359  }
360  "DEV" = @{
361    "aosServers" = @( "AX-DEV-AOS-01")
362    "aosServiceUser" = "CONNECT\AXAOSACC"
363    "enterprisePortalUrl" = "http://ax-uat-ent-01:93/sites/DynamicsAx"
364    "reportingServers" = "AX-DEV-RS-01"
365    "ServerURL" = "ReportServer"
366    "ManagerURL" = "Reports"
367    "RSInstance" = "MSSQLSERVER"
368    "databaseServer" = @( "AX-UAT-SQL-01\SQL_AX_DEV1")
369    "modelDatabaseName" = "AX63_CDS_DEV_model"
370    "transDatabaseName" = "AX63_CDS_DEV"
371    "configuration" = "DEV-client.axc"
372    "serverBinDir" = "C:\Program Files\Microsoft Dynamics AX\60\Server\AX63_CDS_DEV\bin"
373    "TransSource" = "\\AX-UAT-SQL-01\DEV-Backup$\AX63_CDS_DEV.bak"
374    "ModelSource" = "\\AX-UAT-SQL-01\DEV-Backup$\AX63_CDS_DEV_model.bak"
375    "DestData" = "D:\Microsoft SQL Server\MSSQL12.SQL_AX_DEV1\MSSQL\DATA\"
376    "DestLogs" = "F:\Microsoft SQL Server\MSSQL12.SQL_AX_DEV1\MSSQL\DATA\"
377    "DestBackup" = "E:\DEV-Backup\"
378  }
379  "UAT" = @{
380    "aosServers" = @( "AX-UAT-AOS-01","AX-UAT-BAT-01")
381    "aosServiceUser" = "CONNECT\AXAOSACC"
382    "enterprisePortalUrl" = "http://ax-uat-ent-01:83/sites/DynamicsAx"
383    "reportingServers" = "AX-UAT-RS-01"
384    "ServerURL" = "ReportServer"
385    "ManagerURL" = "Reports"
386    "RSInstance" = "MSSQLSERVER"
387    "databaseServer" = @( "AX-UAT-SQL-01\SQL_AX_UAT1")
388    "modelDatabaseName" = "AX63_CDS_UAT_model"
389    "transDatabaseName" = "AX63_CDS_UAT"
390    "configuration" = "UAT-client.axc"
391    "serverBinDir" = "C:\Program Files\Microsoft Dynamics AX\60\Server\AX63_CDS_UAT\bin"
392    "TransSource" = "\\AX-UAT-SQL-01\UAT-Backup$\AX63_CDS_UAT.bak"
393    "ModelSource" = "\\AX-UAT-SQL-01\UAT-Backup$\AX63_CDS_UAT_model.bak"
394    "DestData" = "D:\Microsoft SQL Server\MSSQL12.SQL_AX_UAT1\MSSQL\DATA\"
395    "DestLogs" = "F:\Microsoft SQL Server\MSSQL12.SQL_AX_UAT1\MSSQL\DATA\"
396    "DestBackup" = "E:\UAT-Backup\"
397  }
398  "PROD" = @{
399    "aosServers" = @( "AX-AOS-01","AX-AOS-02","AX-AOS-03","AX-AOS-04","AX-AOS-BAT-01")
400    "aosServiceUser" = "CONNECT\AXAOSACC"
401    "enterprisePortalUrl" = "http://ax-ent-01:83/sites/DynamicsAx/"
402    "reportingServers" = "AX-RS-01"
403    "ServerURL" = "ReportServer"
404    "ManagerURL" = "Reports"
405    "RSInstance" = "MSSQLSERVER"
406    "databaseServer" = @( "AX-SQL-00\SQL_AX1")
407    "modelDatabaseName" = "AX63_CDS_PROD_model"
408    "transDatabaseName" = "AX63_CDS_PROD"
409    "configuration" = "production.axc"
410    "serverBinDir" = "C:\Program Files\Microsoft Dynamics AX\60\Server\AX63_CDS_PROD\bin"
411    "TransSource" = "\\AX-SQL-01\PROD-Backup$\AX63_CDS_PROD.bak"
412    "ModelSource" = "\\AX-SQL-01\PROD-Backup$\AX63_CDS_PROD_model.bak"
413    "DestData" = "D:\MSSQL12.SQL_AX1.Data\"
414    "DestLogs" = "F:\MSSQL12.SQL_AX1.Logs\"
415    "DestBackup" = "E:\PROD-Backup\"
416  }
417  "GOLD" = @{
418    "aosServers" = @( "CH-WV-AX-GOLD-T")
419    "aosServiceUser" = "CONNECT\AXDEVAOS"
420    "enterprisePortalUrl" = "http://ax-uat-ent-01:93/sites/DynamicsAx"
421    "reportingServers" = "CH-WV-AX-GOLD-T"
422    "ServerURL" = "ReportServer"
423    "ManagerURL" = "Reports"
424    "RSInstance" = "MSSQLSERVER"
425    "databaseServer" = @( "CH-WV-AX-GOLD-T\AXDEV")
426    "modelDatabaseName" = "MicrosoftDynamicsAX_Model"
427    "transDatabaseName" = "MicrosoftDynamicsAX"
428    "configuration" = "AXGOLD.axc"
429    "serverBinDir" = "C:\Program Files\Microsoft Dynamics AX\60\Server\MicrosoftDynamicsAX\bin"
430    "TransSource" = "\\CH-WV-AX-GOLD-T\GOLD-Backup$\MicrosoftDynamicsAX.bak"
431    "ModelSource" = "\\CH-WV-AX-GOLD-T\GOLD-Backup$\MicrosoftDynamicsAX_Model.bak"
432    "DestData" = "C:\Program Files\Microsoft SQL Server\MSSQL12.AXDEV\MSSQL\DATA\"
433    "DestLogs" = "C:\Program Files\Microsoft SQL Server\MSSQL12.AXDEV\MSSQL\DATA\"
434    "DestBackup" = "C:\GOLD-Backup\"
435  }
436  "CHDEV" = @{
437    "aosServers" = @( "CH-AXDEV-AOS-01")
438    "aosServiceUser" = "CONNECT\AXDEVAOS"
439    "enterprisePortalUrl" = "http://ax-uat-ent-01:93/sites/DynamicsAx"
440    "reportingServers" = "CH-AXDEV-RS-01"
441    "ServerURL" = "ReportServer_REPORTING"
442    "ManagerURL" = "Reports_REPORTING"
443    "RSInstance" = "REPORTING"
444    "databaseServer" = @( "CH-WV-SQL-01\AXDEV")
445    "modelDatabaseName" = "MicrosoftDynamicsAX_Model"
446    "transDatabaseName" = "MicrosoftDynamicsAX"
447    "configuration" = "AX_CH_DEV.axc"
448    "serverBinDir" = "C:\Program Files\Microsoft Dynamics AX\60\Server\MicrosoftDynamicsAX\bin"
449    "TransSource" = "\\CH-WV-SQL-01\CHDEV$\MicrosoftDynamicsAX.bak"
450    "ModelSource" = "\\CH-WV-SQL-01\CHDEV$\MicrosoftDynamicsAX_Model.bak"
451    "DestData" = "E:\MSSQL12.AXDEV\MSSQL\DATA\"
452    "DestLogs" = "F:\MSSQL12.AXDEV\MSSQL\DATA\"
453    "DestBackup" = "C:\Source\CHDEV\"
454  }
455};
456
457# Destination Configuration options
458$dest = $environments[$Target.ToUpperInvariant()]
459$aosInstances = $dest.aosServers
460[string]$dbServer = $dest.databaseServer
461$aosUser = $dest.aosServiceUser
462$reportingServers = $dest.reportingServers
463$enterpriseUrl = $dest.enterprisePortalUrl
464$modelDatabaseName = $dest.modelDatabaseName
465$transDatabaseName = $dest.transDatabaseName
466$serverBinDir = $dest.serverBinDir
467$DestData = $dest.DestData
468$DestLogs = $dest.DestLogs
469$DestBackup = $dest.DestBackup
470$ServerURL  = $dest.ServerURL
471$ManagerURL = $dest.ManagerURL
472$RSInstance = $dest.RSInstance
473
474# Source Configuration options
475$src = $environments[$Source.ToUpperInvariant()]
476$SaosInstances = $src.aosServers
477[string]$SdbServer = $src.databaseServer
478$SaosUser = $src.aosServiceUser
479$SreportingServers = $src.reportingServers
480$SenterpriseUrl = $src.enterprisePortalUrl
481$SmodelDatabaseName = $src.modelDatabaseName
482$StransDatabaseName = $src.transDatabaseName
483$SserverBinDir = $src.serverBinDir
484$STransSource = $src.TransSource
485$SModelSource = $src.ModelSource
486$SDestBackup = $src.DestBackup
487
488# Common Configuration options
489$axServerInstallPath = "C:\Program Files\Microsoft Dynamics AX\60\Server\$transDatabaseName\bin"
490
491##################################################################
492# Importing Required Modules
493##################################################################
494
495Import-Module sqlps DisableNameChecking
496Import-Module "C:\Program Files\Microsoft Dynamics AX\60\ManagementUtilities\Modules\AXUtilLib.Powershell\AXUtilLib.PowerShell.dll"
497Import-Module "C:\Program Files\Microsoft Dynamics AX\60\ManagementUtilities\Modules\Microsoft.Dynamics.AX.Framework.Management\Microsoft.Dynamics.AX.Framework.Management.dll"
498."C:\Program Files\Microsoft Dynamics AX\60\ManagementUtilities\Microsoft.Dynamics.ManagementUtilities.ps1"
499
500##################################################################
501# Main Application Starts Running Here
502##################################################################
503cls
504Write-Host "*************************************************************************"
505Write-Host " Recreate $Target AX from $Source AX"
506Write-Host "*************************************************************************"
507Write-Host
508Write-Host "*************************************************************************"
509Write-Host "Environment Recreation Settings"
510Write-Host "*************************************************************************"
511Write-Host "TARGET                : $Target"
512Write-Host "DB Server             : $dbServer"
513Write-Host "Model DB Name         : $modelDatabaseName"
514Write-Host "Trans DB Name         : $transDatabaseName"
515Write-Host "SOURCE                : $Source"
516Write-Host "DB Server             : $SdbServer"
517Write-Host "Model DB Name         : $SmodelDatabaseName"
518Write-Host "Trans DB Name         : $StransDatabaseName"
519Write-Host "Trans Source DB       : $STransSource"
520Write-Host "Model Source DB       : $SModelSource"
521Write-Host "*************************************************************************"
522try
523{
524  # Step 1 : Check Admin Rights,
525  if (!(Test-AdministratorPrivileges))
526  {
527    Write-Host -ForegroundColor Red "Please run this script with admin privileges"
528    return
529  }
530
531  Push-Location
532
533  # Step 2 : Take new $Source AX Backup
534  Write-Host "Starting $Source AX Transactional Database Backup"
535  Backup-SqlDatabase -CompressionOption On -ServerInstance $SdbServer -Database $StransDatabaseName -BackupFile "$($SDestBackup)$($STransDatabaseName).bak" -Initialize
536  Write-Host "Starting $Source AX ModelStore Database Backup"
537  Backup-SqlDatabase -CompressionOption On -ServerInstance $SdbServer -Database $SModelDatabaseName -BackupFile "$($SDestBackup)$($SModelDatabaseName).bak" -Initialize
538
539  # Step 3 : Stop AOS
540  Write-Host "Stopping AOS Service on Destination!"
541  Stop-Service -Name 'AOS60$01'
542
543  # Step 4  : Backup $Target AX Databases
544  Write-Host "Starting $Target AX Transactional Database Backup"
545  Backup-SqlDatabase -ServerInstance $dbServer -Database $transDatabaseName -BackupFile "$($DestBackup)$($TransDatabaseName).bak" -CompressionOption On -Initialize
546  Write-Host "Starting $Target AX ModelStore Database Backup"
547  Backup-SqlDatabase -ServerInstance $dbServer -Database $ModelDatabaseName -BackupFile "$($DestBackup)$($ModelDatabaseName).bak" -CompressionOption On -Initialize
548
549  # Step 5: Restore(!)
550  Write-Host "Restoring $Source AX Database to $Target AX!!!!"
551  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
552
553  # Step 6: Reconfigure AX Target settings 
554  Write-Host "Reconfiguring AX $Target Settings"
555  Reconfigure-AxConfiguration -ComputerName $ENV:Computername -SqlServerInstance $dbServer -DestTransDB $transDatabaseName -SourceTransDB $StransDatabaseName -ReportServerHost $reportingServers -ServerURL $ServerURL -ManagerURL $ManagerURL -RSInstance $RSInstance
556
557  # Step 7 : Blow away XPIL etc...
558  Write-Host "Clearing $Target XPPIL cache"
559  if (Test-Path -Path "$axServerInstallPath\XPPIL") {
560    Remove-Item -Path "$axServerInstallPath\XPPIL" -Recurse
561  }
562
563  # Step 8 : Restart AOS on Destination
564  Write-Host "Starting AOS Service on Destination!"
565  Start-Service -Name 'AOS60$01'
566
567  # Step 8.1 : Couple of minute timeout to allow the service to fully start and the next steps complete successfully 
568  Write-Host "Waiting 2 Minutes for Service to Fully Start up"
569  Start-Sleep -seconds 120
570                                
571 
572  # Step 9 : Check Business Connector Install and Install if not present.
573  if (!(Test-Path "C:\Program Files\Microsoft Dynamics AX\60\BusinessConnector\Bin\Microsoft.Dynamics.BusinessConnectorNet.dll"))
574  { Write-Host "Installing MS Dynamics AX 2012 R3 Business Connector - Please Wait....."
575    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
576  }
577
578  # Step 10 : If LOCAL or GOLD or CHDEV Desktop Devs are Admin
579  if (($Target -eq "LOCAL" -or $Target -eq "GOLD"-or $Target -eq "CHDEV") -and (!($Source -eq "GOLD" -or $Source -eq "CHDEV")))
580  { Write-Host "Recreating Desktop DEV Sys Admins!"
581    New-AXUser -AccountType WindowsGroup -AXUserId DDEVADM -UserName "AX DesktopDEV - SysAdmins" -UserDomain CONNECT -Company CDS
582    Add-AXSecurityRoleMember -AOTName "-SYSADMIN-" -AXUserId DDEVADM
583  }
584
585  # Step 11 : If DEV - Needs DEV Admin Group
586  if ($Target -eq "DEV")
587  { Write-Host "Enabling DEV RDS Access"
588    New-AXUser -AccountType WindowsGroup -AXUserId ADAXDEV -UserName "AX RDS DEV Access" -UserDomain CONNECT -Company CDS
589    Add-AXSecurityRoleMember -AOTName "SystemUser" -AXUserId ADAXDEV
590    Add-AXSecurityRoleMember -AOTName "HCMEmployee" -AXUserId ADAXDEV
591    Write-Host "Recreating DEV Sys Admins!"
592    New-AXUser -AccountType WindowsGroup -AXUserId DEVADM -UserName "AX DEV - SysAdmins" -UserDomain CONNECT -Company CDS
593    Add-AXSecurityRoleMember -AOTName "-SYSADMIN-" -AXUserId DEVADM
594  }
595
596  if (!($RunHeadless.IsPresent))
597  { Pause }
598}
599catch
600{
601  $exception = $_.Exception | Format-List -Force | Out-String
602  Write-Host $exception
603  if (!($RunHeadless.IsPresent))
604  { Pause }
605}