Restore SQL/BC Database via PowerShell

There are a million scripts out there to Restore SQL/BC Database (Dynamics NAV / Business Central) in the web…

So why shouldn’t there be another one 😛

What it does

It’s a simple PowerShell script iterating over the defined directories and restoring the database to the last transaction log. For me… It’s my “sleep better script” in case e.g. log-shipping didn’t work as expected or just to restore a recent copy of your Business Central database. Another use-case might be an automatism for a nightly copy used by your Business Intelligence department.

The Process

I really like to use log-shipping with our customers. This technology is quite old-fashioned, but it is very suitable as a “basic failover”. It can be set up quickly, has no additional cost and no impact on performance. But in addition, I always back up with e.g. weekly full backups and daily differential backups. This gives me the possibility, in combination with the transaction logs of the log-shipping, to create additional protection or the opportunity to restore the database in another context.

  • Find and Restore last full backup
  • Find and Restore last differential backup
  • Restore all transaction-log since the differential backup

Attention: Be careful not to restore the last transaction log “with recovery” if you additionally would like to restore the tail log afterwards. Only the tail log might contain up to the last transaction. Otherwise, you might not restore all data.

Restore SQL/BC Database using the script

It’s quite straight forward. It should be sufficient to edit the variables in the “Define Variables” section of the script. Just answer the questions in the comments and you should be ready to go. The script is based on the sqlserver PowerShell module by Microsoft

Attention: Always double and triple check your settings… you don’t want to overwrite the wrong database!

# Restore SQL/BC Database
Install-Module -Name SqlServer

##################
# Define variables
##################

# What's the (new) name of the database?
$DatabaseName = "CRONUS_NAV_90_BI"

# Where to find the backup files?
$BackupPath = "E:\SQL-Backup\CRONUS_NAV_90\"
$BackupPathDiff = "E:\SQL-Backup\CRONUS_NAV_90\Differential\"
$TranslogPath = "E:\SQL-Logshipping\"

# Where to place and name the files on the SQL server?
$RelocateDataPath = "G:\SQL\$DatabaseName.mdf"
$RelocateLogPath = "F:\SQL\$DatabaseName.ldf"
    
# What's the (new) logical name of the database files?
$DataFileName = "Demo Database NAV (9-0)_Data"
$LogFileName = "Demo Database NAV (9-0)_Log"

# Restore the last transaction log with or without recovery?
# If $false, the database remains in recovery mode to restore e.g. a taillog
$RestoreWithRecovery = $true


#################
# Restore process
#################

#1 Get last full-backup (better put it into subfolder than check the size)
$SqlBackup = Get-Childitem -path $BackupPath -Filter *.bak | sort -property LastWritetime | Select-Object -Last 1

#2 Restore full-backup with no-recovery
$SqlServerSnapinVersion = (Get-Command Restore-SqlDatabase).ImplementingType.Assembly.GetName().Version.ToString()
$AssemblySqlServerSmoExtendedFullName = "Microsoft.SqlServer.SmoExtended, Version=$SqlServerSnapinVersion, Culture=neutral, PublicKeyToken=89845dcd8080cc91"

$RelocateData = New-Object "Microsoft.SqlServer.Management.Smo.RelocateFile, $AssemblySqlServerSmoExtendedFullName"($DataFileName, $RelocateDataPath)
$RelocateLog = New-Object "Microsoft.SqlServer.Management.Smo.RelocateFile, $AssemblySqlServerSmoExtendedFullName"($LogFileName, $RelocateLogPath)

# Optional - Uncomment if needed
# Kill-Connections -DatabaseName $DatabaseName

Write-Host "Restoring backup $SqlBackup on database $DatabaseName"

Restore-SqlDatabase `
    -ServerInstance "." `
    -Database $DatabaseName `
    -BackupFile $SqlBackup.FullName `
    -RelocateFile @($RelocateData,$RelocateLog) `
    -RestoreAction Database `
    -NoRecovery `
    -ReplaceDatabase | Out-Null

#3 Find and Restore the last differential backup
$SqlDiffBackup = Get-Childitem -path $BackupPathDiff -Filter *.bak | sort -property LastWritetime | where LastWritetime -gt $SqlBackup.LastWritetime | Select-Object -Last 1

Write-Host "Restoring differencial backup $SqlDiffBackup on database $DatabaseName"

Restore-SqlDatabase `
    -ServerInstance "." `
    -Database $DatabaseName `
    -BackupFile $SqlDiffBackup.FullName `
    -RestoreAction Database `
    -NoRecovery | Out-Null

#4 Restore all transaction-logs from the point in time > #3
$SqlLogBackup = Get-Childitem -path $TranslogPath -Filter *.trn | sort -property LastWritetime | where LastWritetime -gt $SqlDiffBackup.LastWritetime 
$SqlLogBackupCount = $SqlLogBackup | Measure-Object

$RestoreFinished = $false
$SqlLogBackup | ForEach-Object {
    Write-Host "Restoring SQL Log $_.Name on database $DatabaseName"
    $LogCounter += 1

    if ($RestoreFinished -eq $false) {
        
        #5 Restore last transaction-log 
        if(($SqlLogBackupCount.Count -eq $LogCounter) -and ($RestoreWithRecovery -eq $true)) {
            Restore-SqlDatabase `
                -ServerInstance "." `
                -Database $DatabaseName `
                -BackupFile $_.FullName `
                -RestoreAction Log | Out-Null
        
            $RestoreFinished = $true
            Write-Host "Database $DatabaseName fully restored and accessible"
        } else {
            Restore-SqlDatabase `
                -ServerInstance "." `
                -Database $DatabaseName `
                -BackupFile $_.FullName `
                -RestoreAction Log `
                -NoRecovery | Out-Null

             $RestoreFinished = ($RestoreWithRecovery -eq $false) -and ($SqlLogBackupCount.Count -eq $LogCounter)
        }
    }
}
Restore SQL/BC Database

Helper Functions

If needed, you could also kill all open sessions and connections to this specific database using this helper function before step #2 in the script.

##################
# Helper functions
##################

function Kill-Connections {
    Param (
            [string]$DatabaseName,
            [string]$DatabaseInstance = "."
        )

    $KillConnectionsSql =
        "
        USE master
        GO
        ALTER DATABASE $DatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE
        GO
        USE master
        GO
        ALTER DATABASE $DatabaseName SET MULTI_USER
        GO
        USE master
        GO
        "
    
    Invoke-Sqlcmd -ServerInstance $DatabaseInstance -Query $KillConnectionsSql 
}

I hope some of you guys could use it. Personally, it already saved one of my customer’s databases.

... is a technical consultant and developer at Comsol Unternehmenslösungen AG in Kronberg/Taunus. Major tasks are the architecture and implementation of complex, usually cross-system applications in and around Microsoft Dynamics 365 Business Central.

Leave a Reply

Your email address will not be published.