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.

-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.

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.

-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.

-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.

-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.

-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.

Outputs

System.Data.DataRow (default output)

Returns one object per backup set found in the backup file. When -Simple is not specified, returns the full DataTable with backup header metadata including:

Default properties:

• DatabaseName: Name of the database that was backed up
• BackupFinishDate: DateTime when the backup completed
• RecoveryModel: Database recovery model (Simple, Full, or BulkLogged)
• BackupSize: Size of the backup (dbasize object with Byte, KB, MB, GB properties)
• CompressedBackupSize: Size of the compressed backup if compression was used (dbasize object)
• DatabaseCreationDate: DateTime the database was created
• UserName: Login that performed the backup
• ServerName: SQL Server instance name where backup was created
• SqlVersion: SQL Server version string (e.g., “SQL Server 2016”, “SQL Server 2019”)
• BackupPath: Full path to the backup file
• FileList: Collection of backup file details (see -FileList for details)

Additional properties from SMO Restore.ReadBackupHeader():

• BackupType: Type of backup (Database, Differential, Log, etc.)
• BackupName: Name given to the backup set
• Position: Position of this backup set within the file (1 for first, 2 for second, etc.)
• DatabaseVersion: Internal database version number
• IsPassword: Whether the backup is password-protected (0 or 1)
• IsCopyOnly: Whether this is a copy-only backup (0 or 1)
• ContinuationFolk: Whether this is a continuation of a previous backup (0 or 1)
• HasBulkLoggedData: Whether the backup contains bulk-logged operations (0 or 1)
• IsSnapshot: Whether this is a snapshot backup (0 or 1)
• IsDamaged: Whether the backup is marked as damaged (0 or 1)
• StarTime: DateTime when backup started
• CompatibilityLevel: Compatibility level of the database
• SoftwareVendorId: Software vendor identifier
• SoftwareVersionMajor: Major version of SQL Server that created backup
• SoftwareVersionMinor: Minor version of SQL Server that created backup
• SoftwareVersionBuild: Build number of SQL Server that created backup
• MachineName: Computer name where backup was created
• Flags: Backup flags and options
• BindingId: Binding ID
• RecoveryFork: Recovery fork identifier
• Collation: Database collation
• FamilyGuid: Family GUID for backup family tracking
• HasBackupChecksums: Whether checksums are present (0 or 1)
• IsSealedBackup: Whether backup is sealed/complete (0 or 1) System.Data.DataRow (when -Simple is specified)

Returns one object per backup set with only essential backup metadata columns:

• DatabaseName: Name of the database that was backed up
• BackupFinishDate: DateTime when the backup completed
• RecoveryModel: Database recovery model (Simple, Full, or BulkLogged)
• BackupSize: Size of the backup (dbasize object with Byte, KB, MB, GB properties)
• CompressedBackupSize: Size of the compressed backup (dbasize object)
• DatabaseCreationDate: DateTime the database was created
• UserName: Login that performed the backup
• ServerName: SQL Server instance name
• SqlVersion: SQL Server version string
• BackupPath: Full path to the backup file System.Data.DataRow (when -FileList is specified)

Returns detailed information about each data and log file contained within the backup set(s):

• LogicalName: Logical name of the file as defined in the database
• PhysicalName: Physical file path where the file is stored
• Type: File type (D for Data, L for Log, etc.)
• FileGroupName: Filegroup that contains this file
• Size: Size of the file in bytes
• MaxSize: Maximum size of the file in bytes
• FileId: File ID number in the database
• CreateLsn: Log sequence number when file was created
• DropLsn: Log sequence number when file was dropped (if applicable)
• UniqueId: Unique identifier for the file
• ReadOnlyLsn: Log sequence number when file became read-only
• ReadWriteLsn: Log sequence number when file became read-write
• BackupSizeInBytes: Size of this file in the backup
• SourceBlockSize: Original block size when file was created
• FileGroupId: ID of the filegroup containing this file
• LogGroupGuid: Identifier for log group
• DifferentialBaseLsn: LSN of the differential base
• DifferentialBaseGuid: GUID of the differential base
• IsReadOnly: Whether the file is read-only (0 or 1)
• IsPresent: Whether the file is present in this backup (0 or 1)
• TdeThumbprint: Transparent Data Encryption thumbprint if applicable