Restoring database .BAK file in Azure SQL Managed instance with PowerShell module dbatools

 

The script will not RESTORE the database, it will generate script for RESTORE and we can copy and paste it in SSMS.

cls
$Username = 'UserNameHere'
$Password = ConvertTo-SecureString -String 'Password' -AsPlainText -Force
$Creds = New-Object `
 -TypeName System.Management.Automation.PSCredential($Username,$Password)
Get-DbaDatabase `
  -sqlinstance 'managedInstanceNameHere.database.windows.net' `
  -SqlCredential $Creds
<#
   The value that we have provided for -Path parameter is need to taken
   from teh .bak file properties url not from container and storage.
#>
Restore-DbaDatabase `
 -SqlInstance 'managedInstanceNameHere.database.windows.net' `
 -SqlCredential $Creds `
 -DatabaseName TEST `
 -Path 'Take the backp file(.bak) path properties URL ' `
 -OutputScriptOnly `
 -Verbose

$Host.UI.RawUI.BufferSize = New-Object Management.Automation.Host.Size(300, 5000)
cls
$DBs = 'dbname'

$DBs | foreach {
    $DB = $_
    ''
    "/*========= Database Name : $($DB) ============*/"
    ''

    $path = -join('\\a\\SQL-Backups\\b\\c\\', $($DB), '\\*.*')

    $FullName = (
        Get-ChildItem -Recurse -Path $path |
        Where-Object {
            $_.Name -match '.' -and
            $_.LastWriteTime -gt '11/20/2025 5:04 AM'
        }
    ).FullName

    foreach($file in $FullName) {
        if($file -match '.bak') {
            Write-Output "EXECUTE AS LOGIN ='SA'"
            Write-Output ""

            Write-Output @"
restore database [$($DB)]`r`nfrom disk ='$($file)'
with Norecovery, stats =2
"@

            Write-Output ""
        }
        else {
            Write-Output @"
restore log [$($DB)] `r`nfrom disk ='$($file)'
with Norecovery, stats =2
"@
        }
    }

    Write-Output ""

    Write-Output @"
ALTER DATABASE [$($DB)] 
SET HADR AVAILABILITY GROUP = [aggroupnamehere];
"@
}

What is the last backup file that is restored.

SELECT
    r.destination_database_name,
    r.restore_date,
    r.backup_set_id,
    b.type AS backup_type,
    b.first_lsn,
    b.last_lsn,
    b.checkpoint_lsn,
    b.database_backup_lsn,
    mf.physical_device_name AS backup_file_path,
    *

FROM msdb.dbo.restorehistory r

JOIN msdb.dbo.backupset b
    ON r.backup_set_id = b.backup_set_id

JOIN msdb.dbo.backupmediafamily mf
    ON b.media_set_id = mf.media_set_id

WHERE r.destination_database_name = 'dbNamehere'

ORDER BY r.restore_date DESC;