How to use Powershell to automate some basic NAV tasks

0 Shares

In this post I want to present some sample Powershell scripts that I created to automate a couple of basic tasks related to Microsoft Dynamics NAV. Please change the parameters before you use them.

Create new NAV 2016 Environment: restores specified database backup, creates NAV Server Instance, starts NAV server Instance

#Parameters
$ServerInstanceName    = "R16-FP1-BUGFIX"                                                #NAV Server Instance Name
$DatabaseServer        = "localhost"                                                    
$DatabaseName          = "R16-FP1-BUGFIX"                                          
$CompanyName           = ""
$ApplicationDB         = ""
$BackupFilePath        = "D:\Databases\R16-FP1-BUGFIX\AUTO-BACKUPS\R16-FP1-ANDREI_db_20161007081747.bak"       #The path of the backup file
$DestinationPath       = "D:\Databases\R16-FP1-BUGFIX"                                       #The path where you want to have the database files
$MngServicesPort       = 7068
$ClientSericesPort     = 7069
$SoapServicesPort      = 7070
$ODataServicesPort     = 7071

#Load Module
Import-Module "${env:ProgramFiles}\Microsoft Dynamics NAV\90\Service\NavAdminTool.ps1"

#Create new database
Write-Host "Restoring db $DatabaseName.."

New-NAVDatabase -FilePath $BackupFilePath -DatabaseName $DatabaseName -DatabaseServer $DatabaseServer -DestinationPath $DestinationPath -Timeout 0

#Create Single Tenant Environment
write-host "Create ServerInstance: $ServerInstanceName"
New-NAVServerInstance `
    -ServerInstance $ServerInstanceName `
    -ManagementServicesPort $MngServicesPort `
    -ClientServicesPort $ClientSericesPort `
    -SOAPServicesPort $SoapServicesPort `
    -ODataServicesPort $ODataServicesPort `
    -DatabaseServer $DatabaseServer `
    -DatabaseName $DatabaseName `
    -ErrorAction Stop

#Start Service
Set-NAVServerInstance $ServerInstanceName -Start

Drop the specified database if it exists, delete database files from the specified Destination Path, remove the specified Nav Server Instance:

#Parameters
$ServerInstanceName    = "R16-FP1"                                                #NAV Server Instance Name
$DatabaseServer        = "localhost"                                                    
$DatabaseName          = "R16-FP1-ANDREI"                                          
$CompanyName           = ""
$ApplicationDB         = ""
$BackupFilePath        = "D:\Databases\R16-FP1\R16-FP1.bak"                   #The path of the backup file
$DestinationPath       = "D:\Databases\R16-FP1"                               #The path where you want to have the database files
$SQLServerInstance     = "TestSQLServerInstance"

#Load Module
Import-Module "${env:ProgramFiles}\Microsoft Dynamics NAV\90\Service\NavAdminTool.ps1"

#Create log with deleted files
$Include = @("*.ldf","*.mdf","*.ndf")
$DestPath2=$DestPath + "\*"
$values = Get-Childitem $DestPath2 -Include $Include
$output = 'C:\LastDeletedFiles_dropbdscriptLOG.txt'
$CurrentDate = Get-Date
$CurrentDate = "Date: " + $CurrentDate.ToString('dd-MM-yyyy') + " Time: " + $CurrentDate.ToString('HH-mm-ss')
$values | Format-Table Fullname | Out-File $output
Add-Content $output $CurrentDate

#Drop SQL Database
Write-Host "Dropping SQL Db $DatabaseName.."
Import-Module SQLPS -DisableNameChecking
$Server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $SQLServerInstance
$DBObject = $Server.Databases[$DatabaseName]
#check database exists on server
if ($DBObject)
{
#instead of drop we will use KillDatabase
#KillDatabase drops all active connections before dropping the database.
$Server.KillDatabase($DatabaseName)
}

#Remove NAV Server Instance
Write-Host "Removing NAV Server Instance: $ServerInstanceName.."
Remove-NAVServerInstance -ServerInstance $ServerInstanceName -ErrorAction SilentlyContinue -Force

#Open text file with deleted files log
invoke-item $output

Backup a SQL Database:

$dt = Get-Date -Format yyyyMMddHHmmss
$dbname= "R16-FP1-ANDREI"
$backupfile = "D:\Databases\R16-FP1-BUGFIX\AUTO-BACKUPS\$($dbName)_db_$($dt).bak"
$sqlServerInstance = "TestSQLServerInstance"

# Load assembly
[System.Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms")

$CreateBAK=[System.Windows.Forms.Messagebox]::Show("Do you want to create backup for db: $($dbname) ?",'Warning', 'YesNo', 'Warning')

#Check the result
IF ($CreateBAK -eq 'Yes')
{

    Backup-SqlDatabase -ServerInstance $sqlServerInstance -Database $dbname -BackupFile $backupfile
    Echo "Backup Created for database: $($dbname)"
    $ShowMSG=[System.Windows.Forms.Messagebox]::Show("Backup of database: $($dbname) Completed !",'Warning')

}
else
{
$ShowMSG=[System.Windows.Forms.Messagebox]::Show("Backup of database: $($dbname) NOT Done !",'Warning')
}

Get the max file from a folder (the last version of a file). This is not related to NAV, but it could be useful to use in some cases, for example when you want to restore the latest backup of a database:
- Script 1: get the max file:
```
$folder = "C:\Users\user\Desktop\TestPS"
$dir = Get-ChildItem $folder
$files = ($dir | where {$_.extension -eq ".txt"}).Name

$maxfile = ($files | Measure-Object -Maximum).Maximum

[System.Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms") 
$message = [System.Windows.Forms.MessageBox]::Show("The *.txt file with the maximum name from location: $($folder) is: `n`n -&amp;gt; $($maxfile)")
```
- Script 2: “CallPSScriptHiddenConsole.ps1”- calls script 1 and hides the PS Console. This can be used with Task Scheduler in Windows
```
$commpath = '"C:\Users\user\Desktop\Max_file from folder.ps1"'
$strCommand = "powershell -WindowStyle hidden -file $($commpath)"

Invoke-Expression $strCommand
```
Create multiple NAV Server Instances from an Excel file: for example you have in an Excel file details about the NAV Server Instances that need to be created, as shown in the below example:

The below script automatically creates the NAV 2016 instances based on the data from an Excel file. This can be improved to work for all NAV versions in the same time:

#NavVersionYear
$NavVersionYear = 2016
$ExcelWorkbookPath = "C:\Users\andreil\Desktop\Nav Instances.xlsx"
$ExcelWorksheetName = "Sheet1"

#Load Module
Import-Module "${env:ProgramFiles}\Microsoft Dynamics NAV\90\Service\NavAdminTool.ps1"

$objExcel = New-Object -ComObject Excel.Application
$WorkBook = $objExcel.Workbooks.Open($ExcelWorkbookPath)
$WorkSheet = $WorkBook.sheets.item($ExcelWorksheetName)
$rows = $WorkSheet.UsedRange.Rows.Count

For ($i=2;$i -le $rows;$i++)

{ 
    $NavVersionYearExcel   = [int]$worksheet.Range("A$i").Text
    $ServerInstanceName    = $worksheet.Range("B$i").Text
    $DatabaseServer        = $worksheet.Range("C$i").Text                                                    
    $DatabaseName          = $worksheet.Range("D$i").Text                                          
    $MngServicesPort       = [int]$worksheet.Range("G$i").Text
    $ClientSericesPort     = [int]$worksheet.Range("H$i").Text
    $SoapServicesPort      = [int]$worksheet.Range("I$i").Text
    $ODataServicesPort     = [int]$worksheet.Range("J$i").Text
    
 If ($NavVersionYearExcel -eq $NavVersionYear)
    { 
        #Create Single Tenant Environment
        write-host "Create ServerInstance: $ServerInstanceName"
        New-NAVServerInstance `
        -ServerInstance $ServerInstanceName `
        -ManagementServicesPort $MngServicesPort `
        -ClientServicesPort $ClientSericesPort `
        -SOAPServicesPort $SoapServicesPort `
        -ODataServicesPort $ODataServicesPort `
        -DatabaseServer $DatabaseServer `
        -DatabaseName $DatabaseName `
        -ErrorAction Stop`
        
        #Start Service
        Set-NAVServerInstance $ServerInstanceName -Start
    }

}

$objExcel.Quit()

0 Shares

How to use Powershell to automate some basic NAV tasks

Related posts

Leave a Reply Cancel reply