In my latest blog in this PowerShell series I showed how to install Dynamics NAV on a remote or local computer with PowerShell.

The next step is to create the databases. To prepare PowerShell I use these functions to prepare for NAV and SQL administration

[code lang=”powershell”]Import-Module ‘C:\Program Files\Microsoft Dynamics NAV\71\Service\NavAdminTool.ps1’
Import-Module SQLPS
[/code]

I use the Demo Database that is included with the Dynamics NAV DVD to create the default tenant database. This PowerShell script restores the database backup to a new name in a given path on a given SQL server.

[code lang=”powershell”]$NewDatabaseName = ‘NAV71_L01_DEF’
$NewDatabasePath = ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA’
$BackupFile = ‘C:\SQLDemoDatabase\CommonAppData\Microsoft\Microsoft Dynamics NAV\71\Database\Demo Database NAV (7-1).bak’
$sqlserver = ‘sqlserver’

$NewDatabaseFullPath = Join-Path $NewDatabasePath $NewDatabaseName
Write-Host Restoring $BackupFile to $NewDatabaseFullPath

#Load the DLLs if not using SQLPS
[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMO’) | out-null
[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SmoExtended’) | out-null

$Server = New-Object Microsoft.SqlServer.Management.Smo.Server $sqlserver
$Restore = New-Object Microsoft.SqlServer.Management.Smo.Restore

#settings for the restore
$Restore.Action = "Database"
$Restore.NoRecovery = $false;
$Restore.ReplaceDatabase = $false;
$RestorePercentCompleteNotification = 5;
$Restore.Devices.AddDevice($BackupFile, [Microsoft.SqlServer.Management.Smo.DeviceType]::File)

#get the db name
$RestoreDetails = $Restore.ReadBackupHeader($server)

#print database name
"Database Name from Backup File : " + $RestoreDetails.Rows[0]["DatabaseName"]

#give a new database name
$OldDatabaseName = $RestoreDetails.Rows[0]["DatabaseName"]
$Restore.Database = $NewDatabaseName

Write-Host Changing Database Name $OldDatabaseName to $NewDatabaseName

$RestoreFile = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile
$RestoreLog = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile

#set file names; use the default database directory
$OldDataFileName = $OldDatabaseName + ‘_Data’
$NewDataFileName = Join-Path $NewDatabasePath $NewDatabaseName
$RestoreFile.LogicalFileName = $OldDataFileName
$RestoreFile.PhysicalFileName = $NewDataFileName + ‘.mdf’
Write-Host Changing Data File $OldDataFileName to $NewDataFileName
$OldLogFileName = $OldDatabaseName + ‘_Log’
$NewLogFileName = Join-Path $NewDatabasePath $NewDatabaseName
$RestoreLog.LogicalFileName = $OldLogFileName
$RestoreLog.PhysicalFileName = $NewLogFileName + ‘.ldf’
Write-Host Changing Log File $OldLogFileName to $NewLogFileName
$Restore.RelocateFiles.Add($RestoreFile)
$Restore.RelocateFiles.Add($RestoreLog)

$Restore.SqlRestore($Server)
write-host "Restore of " $NewDatabaseName "Complete"

# add role membership
$cn = new-object system.data.SqlClient.SqlConnection("Data Source=$sqlserver;Integrated Security=SSPI;Initial Catalog=$NewDatabaseName");
$cn.Open()
$q = "EXEC sp_addrolemember @rolename = N’db_owner’, @membername = N’NT AUTHORITY\NETWORK SERVICE’"
$cmd = new-object "System.Data.SqlClient.SqlCommand" ($q, $cn)
$cmd.ExecuteNonQuery() | out-null
$cn.Close()[/code]

To be able to use this the SQL Management tools must be installed. After the database has been restored the script makes the NETWORK SERVICE account an owner. This can of course be customized to another user(s).

The following steps are needed to set up multi tenancy. First I need a temporary Dynamics NAV service Instance.

[code lang=”powershell”]$ClientPort = 7210
$ServiceInstanceName = ‘NAV71_L01_APP’
$SoapPort = $ClientPort + 1
$ODataPort = $SoapPort + 1
$MgtPort = $ODataPort + 1
$DatabaseName = ‘NAV71_L01_DEF’
$DatabaseServer = ‘sqlserver’
$DatabaseInstance = ”

New-NAVServerInstance -ServerInstance $ServiceInstanceName -ClientServicesCredentialType Windows -ClientServicesPort $ClientPort -DatabaseInstance $DatabaseInstance -DatabaseName $DatabaseName -DatabaseServer $DatabaseServer -ManagementServicesPort $MgtPort -SOAPServicesPort $SoapPort -ODataServicesPort $ODataPort
Set-NAVServerInstance -ServerInstance $ServiceInstanceName -Start[/code]

Next step is to export the application to a separate database and change the service configuration to multi tenant.

[code lang=”powershell”]$ServiceInstance = ‘NAV71_L01_APP’
$DatabaseName = ‘NAV71_L01_DEF’
$DatabaseServer = ‘sqlserver’
$DatabaseInstance = ”

# Stop the NAV Service
Set-NAVServerInstance $ServiceInstance -Stop

# Export the NAV Application to a new Database and then remove
Export-NAVApplication -DatabaseName $DatabaseName -DatabaseServer $DatabaseServer -DatabaseInstance $DatabaseInstance -DestinationDatabaseName $ServiceInstance
Remove-NAVApplication -DatabaseName $DatabaseName -DatabaseServer $DatabaseServer -DatabaseInstance $DatabaseInstance -Force

# Change Service to MultiTenant
Set-NAVServerConfiguration -ServerInstance $ServiceInstance -KeyName MultiTenant -KeyValue "true"
Set-NAVServerConfiguration -ServerInstance $ServiceInstance -KeyName DatabaseName -KeyValue ""
Set-NAVServerInstance $ServiceInstance -Start

# Mount Application
Mount-NAVApplication -DatabaseServer $DatabaseServer -DatabaseInstance $DatabaseInstance -DatabaseName $ServiceInstance -ServerInstance $ServiceInstance
Mount-NAVTenant -ServerInstance $ServiceInstance -Id Default -DatabaseServer $DatabaseServer -DatabaseInstance $DatabaseInstance -DatabaseName $DatabaseName -OverwriteTenantIdInDatabase -AllowAppDatabaseWrite

Get-NAVTenant -ServerInstance $ServiceInstance | Format-Table
[/code]

I like to create empty tenant databases that I can later pick up and use for customers. Here I create ten databases and attach them to the service to initialize the common tables.

[code lang=”powershell”]$ServiceInstance = ‘NAV71_L01_APP’
$DatabaseServer = ‘sqlserver’
$DatabaseInstance = ”
$TenantPrefix = ‘NAV71_L01_’

#Load the DLLs if not using SQLPS
[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMO’) | out-null
[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SmoExtended’) | out-null

$Server = New-Object Microsoft.SqlServer.Management.Smo.Server $DatabaseServer

$NewIds = (’00’,’01’,’02’,’03’,’04’,’05’,’06’,’07’,’08’,’09’)

foreach ($NewId in $NewIds)
{
$DatabaseName = $TenantPrefix + $NewId
$db = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Database($DatabaseServer, $DatabaseName)
$db.RecoveryModel = ‘full’
$db.Create()

# add role membership
$cn = new-object system.data.SqlClient.SqlConnection("Data Source=$DatabaseServer;Integrated Security=SSPI;Initial Catalog=$DatabaseName");
$cn.Open()
$q = "EXEC sp_addrolemember @rolename = N’db_owner’, @membername = N’NT AUTHORITY\NETWORK SERVICE’"
$cmd = new-object "System.Data.SqlClient.SqlCommand" ($q, $cn)
$cmd.ExecuteNonQuery() | out-null
$cn.Close()

Mount-NAVTenant -ServerInstance $ServiceInstance
-Id $NewId

-DatabaseServer $DatabaseServer
-DatabaseInstance $DatabaseInstance

-DatabaseName $DatabaseName `
-OverwriteTenantIdInDatabase
}[/code]

Since I did all this on my developement machine I wanted to move all the databases to the production SQL server. So I created a script to backup all the databases.

[code lang=”powershell”]$BackupLocation = ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\’

$appDatabases = Get-NAVServerInstance | Where-Object {$_.DisplayName -match ‘NAV71_’} | Get-NAVApplication
foreach ($appDatabase in $appDatabases)
{
$dbName = $appDatabase.’Database name’
$dbServer = $appDatabase.’Database server’
$backupFile = $BackupLocation + $dbName + ‘.bak’
write-host "Backing up database $dbName on $dbServer"
Backup-SqlDatabase -ServerInstance $dbServer -Database $dbName -BackupAction Database -BackupFile $backupFile
}
$tenantDatabases = Get-NAVServerInstance | Where-Object {$_.DisplayName -match ‘NAV71_’} | Get-NAVTenant
foreach ($tenantDatabase in $tenantDatabases)
{
$dbName = $tenantDatabase.DatabaseName
$dbServer = $tenantDatabase.DatabaseServer
$backupFile = $BackupLocation + $dbName + ‘.bak’
write-host "Backing up database $dbName on $dbServer"
Backup-SqlDatabase -ServerInstance $dbServer -Database $dbName -BackupAction Database -BackupFile $backupFile
}
[/code]

And the last step is to stop and remove the temporary Dynamics NAV service instance.

[code lang=”powershell”]$ServiceInstanceName = ‘NAV71_L01_APP’

Set-NAVServerInstance -ServerInstance $ServiceInstanceName -Stop
Remove-NAVServerInstance -ServerInstance $ServiceInstanceName -Force
[/code]

On the production SQL server I restored the databases with this script. I also give the service user owner rights to the databases.

[code lang=”powershell”]#Load the DLLs if not using SQLPS
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
#Need SmoExtended for smo.backup
[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

$Backups = dir ‘T:\Recover\Nav71\*.bak’
$NewDatabasePath = ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA’
$sqlserver = ‘sqlserver’
$ServiceAccount = ‘DOMAIN\SERVICE USER’

foreach ($BackupFile in $Backups) {
‘Restoring ‘ + $BackupFile.Directory + ‘\’ + $BackupFile.Name

$server = New-Object Microsoft.SqlServer.Management.Smo.Server $sqlserver
$backupDevice = New-Object Microsoft.SqlServer.Management.Smo.BackupDeviceItem $BackupFile, "File"
$Restore = New-Object Microsoft.SqlServer.Management.Smo.Restore

#Set properties for Restore
$Restore.NoRecovery = $false;
$Restore.ReplaceDatabase = $true;
$Restore.Devices.Add($backupDevice)
$RestoreDetails = $Restore.ReadBackupHeader($server)
$NewDatabaseName = Get-ChildItem $BackupFile | % {$_.BaseName}
$Restore.Database = $NewDatabaseName

# Specify the need to relocate the data and log files (mdf and ldf)
$resFile = New-Object("Microsoft.SqlServer.Management.Smo.RelocateFile")
$resLog = New-Object("Microsoft.SqlServer.Management.Smo.RelocateFile")

# The logical file names should be the logical filename stored in the backup media
# The physical file should reflect the updated path
$resFile.LogicalFileName = $RestoreDetails.Rows[0]["DatabaseName"]
$resLog.LogicalFileName = $RestoreDetails.Rows[0]["DatabaseName"] + "_Log"
$resFile.PhysicalFileName = $NewDatabasePath + $RestoreDetails.Rows[0]["DatabaseName"] + "_Data.mdf"
$resLog.PhysicalFileName = $NewDatabasePath + $RestoreDetails.Rows[0]["DatabaseName"] + "_Log.ldf"
$Restore.RelocateFiles.Add($resFile)
$Restore.RelocateFiles.Add($resLog)

$Restore.SqlRestore($server)
# add role membership
$cn = new-object system.data.SqlClient.SqlConnection("Data Source=$sqlserver;Integrated Security=SSPI;Initial Catalog=$NewDatabaseName");
$cn.Open()
$q = "EXEC sp_addrolemember @rolename = N’db_owner’, @membername = N’$ServiceAccount’"
$cmd = new-object "System.Data.SqlClient.SqlCommand" ($q, $cn)
$cmd.ExecuteNonQuery() | out-null
$cn.Close()
}
[/code]

Next we take a look at creating the server instance on a remote machine, mounting the tenant and creating the ClickOnce website along with the Web Client.

3 thoughts on “Restore Database with PowerShell and create Tenants

  1. waldo1001 says:

    nice one! 🙂
    I have a Backup/Restore story ready as well (will be published soon).. the more info the marrier 🙂

  2. laurent says:

    dear Gunnar
    I tried your script but I have always same error on second script
    Exception calling “ReadBackupHeader” with “1” argument(s): “Failed to connect to server mssqlserver.”
    At C:0-Jalix2-Scripts\CLOUD PME\Street and house2-restore_database_with_new_name.ps1:24 char:1
    + $RestoreDetails = $Restore.ReadBackupHeader($server)
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : ConnectionFailureException

  3. laurent says:

    I think I know why because line
    $sqlserver = ‘sqlserver’
    must be
    $sqlserver = ”
    in my case

Leave a Reply

%d bloggers like this: