Using PowerShell to Refresh a Microsoft Dynamics Business Central On Prem Test Database
I don’t blog very often, but when I do, I try to include some sort of catchy image as part of the intro. A brief search of the Googles for the word, “refreshing” was disappointing; images ranged from fruity and fizzy boozy drinks to platitudes along the “Live Love Laugh” genre. I can’t work with either of those. So, straight to business.
Stopping Service Tiers. SQL backups and restores. Fiddling with company names and red warning banners so users know they are in a Test database. While these steps might be super enjoyable due to their monotony and ease of missing an important step, I thought I’d try to put together a PowerShell script to streamline the process. I realize this removes all the fun.
This article is only for on-premise installations of Business Central (BC). The cloud/SaaS version requires a completely different method of refreshing involving Sandboxes. The steps below might also work for older versions of NAV, but I have not done any regression testing.
The basic steps for a Test database refresh are as follows:
- SQL backup of production
- Stop Test service tier
- SQL restore to Test
- Restart Test service tier
- Rename production company name to test company name
- Add a banner/warning that indicates a Test database
All these steps can be done with PowerShell except for the last – adding a banner requires running some BC code to update Company Information.
There are two dependencies that need to be installed in order for this to work. First is the Business Central Administration Shell, which is part of the Business Central Installation kit. That option is highlighted in the screen snap below:
Figure 1 – Microsoft Dynamics Business Administration Shell
Second is the SQL Server PowerShell Module. This can be safely installed on your Service Tier server without installing any of the other SQL tools. You don’t even need SQL Server Management Studio; the PowerShell module is stand-alone.
- Open a PowerShell window as administrator
- See if the SQL module is already installed
                                 get-module sqlserver -listavailable
- If nothing shows up, you need to install it.
                                 install-module -name sqlserver
Log on to your Service Tier server. Open the Business Central Administration Shell as an Administrator. I’m going to go step by step to describe what is going on, but I will include the full PowerShell script and AL code at the end of the article.
- SQL Backup of Production
Full documentation for the Backup PowerShell command is here:
https://docs.microsoft.com/en-us/powershell/module/sqlserver/backup-sqldatabase?view=sqlserver-ps
Backup-SqlDatabase `
 -ServerInstance “yoursqlserver.yourdomain.com” `
 -Database “Business Central Production” `
 -BackupFile “fileshareserverfileshareTestRestore.bak” `
 -BackupAction Database `
 -CopyOnly `
 -CompressionOption on `
 -Initialize
Â
The backup command will need to be edited to match your environment. The notable option is CopyOnly: this option is important because it will leave the SQL transaction log alone and won’t mess up any automated scheduled backups.
- Stop Test Service Tier
Get-Service ‘MicrosoftDynamicsNavServer$BCTest’ `
-ComputerName “test-service-tier.yourdomain.com” | Stop-Service
- SQL Restore to Test
Full docs for the Restore command:
https://docs.microsoft.com/en-us/powershell/module/sqlserver/restore-sqldatabase?view=sqlserver-ps
Restore-SqlDatabase `
 -ServerInstance “yoursqlserver.yourdomain.com” `
 -Database “Business Central Test” `
 -BackupFile “fileshareserverfileshareTestRestore.bak” `
 -RestoreAction Database `
 -ReplaceDatabase `
 -Confirm
Â
I am huge fan of Confirm, even though most people will blast right past the prompt to get the default.
- Restart Test Service Tier
Get-Service ‘MicrosoftDynamicsNavServer$BCTest’ `
-ComputerName “test-service-tier.yourdomain.com” | Start-Service
Â
- Rename Production Company Name to Test Company Name
This takes two steps.
Rename-NAVCompany `
 -ServerInstance “BCTest” `
 -CompanyName “BC Production” `
 -NewCompanyName “Test BC Test” `
 -Verbose
Set-NAVCompany `
 -ServerInstance “BCTest” `
 -CompanyName “Test BC Test” `
 -CompanyDisplayName “TEST BC TEST”
Â
- Add a banner / warning that indicates a Test database
I could not figure out a way to do this with PowerShell, since this step requires updating Company Information inside of BC. You can consider this an optional step, since BC 15 only allows a four-character Badge, so this may not be worth your effort. This is the AL codeunit, but this can be re-written in C/AL for older versions. Put this in its own Extension so it can be published as a stand-alone tool.
codeunit 50107 “ARC PowerShell Tools”
{
   procedure SetTestCompanyBadge()
   var
       CompanyInfo: Record “Company Information”;
   begin
       with CompanyInfo do begin
           Get();
           “System Indicator” := “System Indicator”::Custom;
           “System Indicator Style” := “System Indicator Style”::Accent6;
           “Custom System Indicator Text” := ‘TEST’;
           Modify(true);
       end;
   end;
}
Â
Call this codeunit from PowerShell:
Â
Invoke-NAVCodeunit `
 -ServerInstance “BCTest” `
 -CompanyName “Test BC Test” `
 -CodeunitId 50107 `
 -MethodName “SetTestCompanyBadge”
Â
This is the full PowerShell Script:
Â
Backup-SqlDatabase `
 -ServerInstance “yoursqlserver.yourdomain.com” `
 -Database “Business Central Production” `
 -BackupFile “fileshareserverfileshareTestRestore.bak” `
 -BackupAction Database `
 -CopyOnly `
 -CompressionOption on `
 -Initialize
Â
Get-Service ‘MicrosoftDynamicsNavServer$BCTest’ `
 -ComputerName “test-service-tier.yourdomain.com” | Stop-Service
Â
Restore-SqlDatabase `
 -ServerInstance “yoursqlserver.yourdomain.com” `
 -Database “Business Central Test” `
 -BackupFile “fileshareserverfileshareTestRestore.bak” `
 -RestoreAction Database `
 -ReplaceDatabase `
 -Confirm
Â
Get-Service ‘MicrosoftDynamicsNavServer$BCTest’ `
 -ComputerName “test-service-tier.yourdomain.com” | Start-Service
Â
Rename-NAVCompany `
 -ServerInstance “BCTest” `
 -CompanyName “BC Production” `
 -NewCompanyName “Test BC Test” `
 -Verbose
Set-NAVCompany `
 -ServerInstance “BCTest” `
 -CompanyName “Test BC Test” `
 -CompanyDisplayName “TEST BC TEST”
Â
Invoke-NAVCodeunit `
 -ServerInstance “BCTest” `
 -CompanyName “Test BC Test” `
 -CodeunitId 50107 `
 -MethodName “SetTestCompanyBadge”
Â
If you have any questions about Dynamics NAV or Business Central questions for any version, contact ArcherPoint.
Read more “How To” blogs from ArcherPoint for practical advice on using Microsoft Dynamics Business Central or NAV.
If you are interested in NAV/Business Central development, check out our collection of NAV Development Blogs.