A Technical Recipe for Success: How to Restore a Microsoft Dynamics NAV 2015 Database with Powershel


The SQL Server Management Studio is not installed by default when using the NAV 2015 VM Image so I decided to create a Powershell script to restore a Microsoft Dynamics NAV 2015 database to a new database and create the related Service Instance.


The Powershell script has three parameters:

  1. BackupFile – SQL Backup file name including file path.

  2. DatabaseName – The name of the New Microsoft Dynamics NAV database.

  3. DatabaseServer – The name of the SQL Server instance for the Microsoft Dynamics NAV database to use.

EXAMPLE:

ABS-RestoreNAV2015DB -BackupFile C:ABSDemo.bak -DatabaseName ‘YourDBName’ -DatabaseServer localhostNAVDEMO


When the Powershell script begins it first creates a New SQL Database Based upon the Default File and Log Settings found in the Master Database.

Once the database is created it restores the Backup File specified. Once the backup has been restored the script then creates a service tier. Finally the service tier is started and you are ready to run the newly created Database.

With NAV 2015 you now have more power than ever before with Powershell.

You can download the Powershell script below.

function ABS-RestoreNAV2015DB

{

<#

.SYNOPSIS

This Windows Powershell script shows how to Restore a Microsoft Dynamics NAV 2015 database to a New database and Create the related Service Instance.

.DESCRIPTION

The ABS-RestoreNAV2015DB cmdlet Restores SQL Server Backup Of NAV, Creates Service Tier, Starts New Service Tier.

.PARAMETER DatabaseServer

  Specifies the name of the SQL Server instance for the Microsoft Dynamics NAV database to use.

.PARAMETER DatabaseName

  Specifies the name of the New Microsoft Dynamics NAV database.

.PARAMETER BackupFile

  Specifies the SQL Backup file name including file path.

.EXAMPLE

ABS-RestoreNAV2015DB -BackupFile C:ABSDemo.bak -DatabaseName ‘YourDBName’ -DatabaseServer localhostNAVDEMO

.NOTES

.LINK

#>

    [CmdletBinding()]

    param (

        [parameter(Mandatory=$true, ValueFromPipelineByPropertyName = $true)]

        [ValidateNotNullOrEmpty()]

        [string]$BackupFile,

        [parameter(Mandatory=$true, ValueFromPipelineByPropertyName = $true)]

        [string]$DatabaseName,

        [parameter(Mandatory=$false, ValueFromPipelineByPropertyName = $true)]

        [ValidateNotNullOrEmpty()]

        [string]$DatabaseServer = “localhostNAVDEMO”

    )

    BEGIN

    {

        # Load The Assembly Need For SQL Management

        [System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SMO”) | Out-Null

        [System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SmoExtended”) | Out-Null

        [System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.ConnectionInfo”) | Out-Null

        [System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SmoEnum”) | Out-Null

    }

    PROCESS

    {

      # Connect to the specified instance

        $srv = new-object (‘Microsoft.SqlServer.Management.Smo.Server’) $DatabaseServer

        # Get the default file and log locations

        # (If DefaultFile and DefaultLog are empty, use the MasterDBPath and MasterDBLogPath values)

        $fileloc = $srv.Settings.DefaultFile

        $logloc = $srv.Settings.DefaultLog

        if ($fileloc.Length -eq 0) {

            $fileloc = $srv.Information.MasterDBPath

            }

        if ($logloc.Length -eq 0) {

            $logloc = $srv.Information.MasterDBLogPath

            }

        # Build the physical file names for the database copy

        $dbfile = $fileloc +  $DatabaseName + ‘_Data.mdf’

        $logfile = $logloc +  $DatabaseName + ‘_Log.ldf’

        Write-Host “Restoring…” $dbfile

        # Use the backup file name to create the backup device

        $bdi = new-object (‘Microsoft.SqlServer.Management.Smo.BackupDeviceItem’) ($BackupFile, ‘File’)

        # Create the new restore object, set the database name and add the backup device

        $rs = new-object(‘Microsoft.SqlServer.Management.Smo.Restore’)

        $rs.Database = $DatabaseName

        $rs.Devices.Add($bdi)

        # Get the file list info from the backup file

        $fl = $rs.ReadFileList($srv)

        foreach ($fil in $fl) {

            $rsfile = new-object(‘Microsoft.SqlServer.Management.Smo.RelocateFile’)

            $rsfile.LogicalFileName = $fil.LogicalName

            if ($fil.Type -eq ‘D’){

                $rsfile.PhysicalFileName = $dbfile

                }

            else {

                $rsfile.PhysicalFileName = $logfile

                }

            $rs.RelocateFiles.Add($rsfile)

            }

        # Restore the database

        $rs.SqlRestore($srv)

        Write-Host “…SQL Database”$DatabaseName” Restore Complete”

        # Get NAV Services

        Import-Module “C:Program FilesMicrosoft Dynamics NAV80ServiceMicrosoft.Dynamics.Nav.Management.dll”

        $NavServerInstance = Get-NavServerInstance

        Foreach ($ServerInstance in $NavServerInstance)

        {

            $LastCreatedServerInstance = $ServerInstance.ServerInstance

        }

        $NAVServerConfiguration = GET-NAVServerConfiguration $LastCreatedServerInstance

        Foreach ($Key in ($NAVServerConfiguration | Where-Object {$_.Key -eq “ManagementServicesPort”}))

        {$StartingPort = [int]$Key.Value + 100}

        # Get Last Service Instace To Define Where To Start

        $ManagementServicesPort = [int]$StartingPort

        $ClientServicesPort  = [int]$StartingPort +1

        $SOAPServicesPort  = [int]$StartingPort +2

        $ODataServicesPort  = [int]$StartingPort +3

        Write-Host “…Creating NAV Service Instance” $DatabaseName

        Write-Host ”   ManagementServicesPort = ” $ManagementServicesPort

        Write-Host ”   ClientServicesPort = ” $ClientServicesPort

        Write-Host ”   SOAPServicesPort = ” $SOAPServicesPort

        Write-Host ”   ODataServicesPort = ” $ODataServicesPort

        New-NAVServerInstance -ServiceAccount ‘1’ -ServerInstance $DatabaseName -DatabaseInstance ” -DatabaseName $DatabaseName   -DatabaseServer ‘localhost’   -ManagementServicesPort $ManagementServicesPort -ClientServicesPort $ClientServicesPort -SOAPServicesPort $SOAPServicesPort -ODataServicesPort $ODataServicesPort

        #Start Last Created Service Instance

        $NavServerInstance = Get-NavServerInstance

        $LastCreatedServerInstance = $NavServerInstance[$NavServerInstance.GetUpperBound(0)].ServerInstance

        Write-Host “…Starting NAV Service Instance” $LastCreatedServerInstance

        Set-NavServerInstance $LastCreatedServerInstance -start 

    }

}

ABS-RestoreNAV2015DB

Microsoft Gold Partner Logo_edited.png
  • LinkedIn Social Icon
  • Twitter Social Icon
  • Facebook Social Icon
  • YouTube Social  Icon

Austin  |  Dallas  |  Houston  |  New Orleans  |  Nashville