Tuesday, December 14, 2021

PowerShell and SQL scripts for Database Refreshing in a Devbox

 Say you created a new database AxDB_TEST2 and restored a test environment backup there.

Now you need to change the databases names so that D365FO would target a restored data.

First, you need to stop D365FO services, for example, with a similar PowerShell script.

function StopD365RelevantService()
{
    $services = "DynamicsAxBatch","Microsoft.Dynamics.AX.Framework.Tools.DMF.SSISHelperService.exe","MR2012ProcessService","LCSDiagnosticClientService"
    foreach ($item in $services)
    {
      Set-Service -Name $item -StartupType Disabled  
    }
    Stop-Service -Name Microsoft.Dynamics.AX.Framework.Tools.DMF.SSISHelperService.exe -ErrorAction SilentlyContinue
    Stop-Service -Name DynamicsAxBatch -ErrorAction SilentlyContinue
    Stop-Service -Name W3SVC -ErrorAction SilentlyContinue
    Stop-Service -Name MR2012ProcessService -ErrorAction SilentlyContinue
    Stop-Service -Name LCSDiagnosticClientService -ErrorAction SilentlyContinue

    Set-Service -Name W3SVC -StartupType Automatic
}



Then you can use the following SQL script to 'exchange' two databases.

use master
ALTER DATABASE AxDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE    
ALTER DATABASE AxDB MODIFY NAME = AxDB_ORIG
ALTER DATABASE AxDB_ORIG SET MULTI_USER

ALTER DATABASE AxDB_TEST2 SET SINGLE_USER WITH ROLLBACK IMMEDIATE    
ALTER DATABASE AxDB_TEST2 MODIFY NAME = AxDB
ALTER DATABASE AxDB SET MULTI_USER

Once it is done, get the services back to life

function StartD365RelevantService()
{
    #Set-Service -Name "DynamicsAxBatch","Microsoft.Dynamics.AX.Framework.Tools.DMF.SSISHelperService.exe","W3SVC","MR2012ProcessService" -StartupType Automatic
    $services = "DynamicsAxBatch","Microsoft.Dynamics.AX.Framework.Tools.DMF.SSISHelperService.exe","W3SVC","MR2012ProcessService","LCSDiagnosticClientService"
    foreach ($item in $services)
    {
      Set-Service -Name $item -StartupType Automatic  
    }
    #Set-Service -Name 'DynamicsAxBatch' -StartupType Automatic
    Start-Service -Name Microsoft.Dynamics.AX.Framework.Tools.DMF.SSISHelperService.exe -ErrorAction SilentlyContinue
    Start-Service -Name DynamicsAxBatch -ErrorAction SilentlyContinue
    Start-Service -Name W3SVC -ErrorAction SilentlyContinue
    Start-Service -Name MR2012ProcessService -ErrorAction SilentlyContinue
    Stop-Service -Name LCSDiagnosticClientService -ErrorAction SilentlyContinue
}

No comments: