commands

^

Read-DbaBackupHeader

Author Chrissy LeMaire (@cl), netnerds.net
Availability Windows, Linux, macOS

 

Want to see the source code for this command? Check out Read-DbaBackupHeader on GitHub.
Want to see the Bill Of Health for this command? Check out Read-DbaBackupHeader.

Synopsis

Extracts backup metadata from SQL Server backup files without restoring them

Description

Uses SQL Server's RESTORE HEADERONLY functionality to extract detailed metadata from backup files including database name, backup type, creation date, file lists, and backup size information. This lets you validate backups, plan restores, and audit backup inventory without actually performing a restore operation.

The function can process full, differential, and transaction log backups from local file systems, network shares, and Azure blob storage. It requires an online SQL Server instance to parse the backup files since it leverages SQL Server's built-in backup reading capabilities.

Supports multithreaded processing for improved performance when scanning multiple backup files. The backup file paths must be accessible from the target SQL Server instance, not your local workstation.

Syntax

Read-DbaBackupHeader
    [-SqlInstance] <DbaInstanceParameter>
    [[-SqlCredential] <PSCredential>]
    [-Path] <Object[]>
    [-Simple]
    [-FileList]
    [[-AzureCredential] <String>]
    [-EnableException]
    [<CommonParameters>]

 

Examples

 

Example: 1
PS C:\> Read-DbaBackupHeader -SqlInstance sql2016 -Path S:\backups\mydb\mydb.bak

Logs into sql2016 using Windows authentication and reads the local file on sql2016, S:\backups\mydb\mydb.bak.
If you are running this command on a workstation and connecting remotely, remember that sql2016 cannot access files on your own workstation.

Example: 2
PS C:\> Read-DbaBackupHeader -SqlInstance sql2016 -Path \\nas\sql\backups\mydb\mydb.bak, \\nas\sql\backups\otherdb\otherdb.bak

Logs into sql2016 and reads two backup files - mydb.bak and otherdb.bak. The SQL Server service account must have rights to read this file.

Example: 3
PS C:\> Read-DbaBackupHeader -SqlInstance . -Path C:\temp\myfile.bak -Simple

Logs into the local workstation (or computer) and shows simplified output about C:\temp\myfile.bak. The SQL Server service account must have rights to read this file.

Example: 4
PS C:\> $backupinfo = Read-DbaBackupHeader -SqlInstance . -Path C:\temp\myfile.bak
PS C:\> $backupinfo.FileList

Displays detailed information about each of the datafiles contained in the backupset.

Example: 5
PS C:\> Read-DbaBackupHeader -SqlInstance . -Path C:\temp\myfile.bak -FileList

Also returns detailed information about each of the datafiles contained in the backupset.

Example: 6
PS C:\> "C:\temp\myfile.bak", "\backupserver\backups\myotherfile.bak" | Read-DbaBackupHeader -SqlInstance sql2016  | Where-Object { $_.BackupSize.Megabyte -gt 100 }

Reads the two files and returns only backups larger than 100 MB

Example: 7
PS C:\> Get-ChildItem \\nas\sql\*.bak | Read-DbaBackupHeader -SqlInstance sql2016

Gets a list of all .bak files on the \nas\sql share and reads the headers using the server named "sql2016". This means that the server, sql2016, must have read access to the \nas\sql share.

Example: 8
PS C:\> Read-DbaBackupHeader -SqlInstance sql2016 -Path https://dbatoolsaz.blob.core.windows.net/azbackups/restoretime/restoretime_201705131850.bak -AzureCredential AzureBackupUser

Gets the backup header information from the SQL Server backup file stored at https://dbatoolsaz.blob.core.windows.net/azbackups/restoretime/restoretime_201705131850.bak on Azure

Required Parameters

-SqlInstance

The target SQL Server instance or instances.

Alias
Required True
Pipeline false
Default Value
-Path

Specifies the file path to SQL Server backup files including full, differential, and transaction log backups. Supports local paths, UNC network shares, and Azure blob storage URLs.
The backup files must be accessible from the target SQL Server instance, not your local workstation. Use this to read backup metadata without performing an actual restore.

Alias
Required True
Pipeline true (ByValue)
Default Value

Optional Parameters

-SqlCredential

Login to the target instance using alternative credentials. Accepts PowerShell credentials (Get-Credential).
Windows Authentication, SQL Server Authentication, Active Directory - Password, and Active Directory - Integrated are all supported.
For MFA support, please use Connect-DbaInstance.

Alias
Required False
Pipeline false
Default Value
-Simple

Returns a simplified output with only essential columns: DatabaseName, BackupFinishDate, RecoveryModel, BackupSize, CompressedBackupSize, DatabaseCreationDate, UserName, ServerName, SqlVersion, and
BackupPath.
Use this when you need a quick overview of backup files without the full 50+ columns of detailed metadata.

Alias
Required False
Pipeline false
Default Value False
-FileList

Returns detailed information about each data and log file contained within the backup set, including logical names, physical paths, file sizes, and file types.
Use this when planning restores to different locations or when you need to understand the file structure before performing a restore operation.

Alias
Required False
Pipeline false
Default Value False
-AzureCredential

Specifies the name of a SQL Server credential object that contains the authentication information for accessing Azure blob storage.
Required when reading backup files stored in Azure blob storage. The credential must already exist on the target SQL Server instance and contain valid Azure storage account keys or SAS tokens.

Alias
Required False
Pipeline false
Default Value
-EnableException

By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message.
This avoids overwhelming you with "sea of red" exceptions, but is inconvenient because it basically disables advanced scripting.
Using this switch turns this "nice by default" feature off and enables you to catch exceptions with your own try/catch.

Alias
Required False
Pipeline false
Default Value False