Restore SQL/BC Database via PowerShell

# 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)

$RelocateData = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile($DataFileName, $RelocateDataPath)
$RelocateLog = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile($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)
        }
    }
}

##################
# 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 
}