Day 13 of 31 Days of SQL Server Backup and Restore using PowerShell: Reading the contents of a Backup File

Yesterday we saw how easy it is to restore a SQL Server database from backups with PowerShell if we have a folder that contains:

• Only SQL backup files
• All files are in chronological/LSN order
• The oldest file is the Full backup
• There are no differential or partial backups in the mix
• Only one database’s backups
• Each backup file only contains 1 backups

Unfortunately we don’t always get that. So we need to be able to find out what we have and work out the the best way to restore our database. Over the next 2 days I’m going to introduce how we can read all the files we have, extract the information we need and then store it in a custom PowerShell object so we can manipulate it for ourselves. We will then look at how we can populate this object if we’re lucky enough to also have access to the original server.

Today we’ll look at reading the contents of the backup files and finding out what’s inside.

Let’s start with just working out which files in a folder are actually of interest to us. Let’s start with the worst case scenario; a folder full of files, some of which may be SQL Server backups of one kind or another, but someone hasn’t put file extensions on or has been very lax with which ones they have done. Like so:

PS C:\psbackups> Get-ChildItem

    Directory: C:\psbackups

Mode          LastWriteTime     Length Name
----          -------------     ------ -----

-a---  14/09/2013     07:46          0 file1
-a---  14/09/2013     07:46    5605888 file2

PS C:\psbackups>

OK, in this example you can probably deduce which one is not a backup file! But if you had folders full of these how would you work out which ones were of interest? Well, you’d use the ReadMediaHeader method of your $Restore object to quickly try to read the header that SQL Server writes at the start of all it’s backup files. This is a very quick operation, even on huge backup files as it’s at the start of the file. Unfortunately, ReadMediaHeader doesn’t return a nice “No this isn’t a SQL Server backup” message, so we have to wrap it in a Try, Catch, Finally block and handle it ourselves:

import-module "SQLPS" -DisableNameChecking

$sqlsvr = New-Object -TypeName  Microsoft.SQLServer.Management.Smo.Server("WIN-C0BP65U3D4G")
$restore = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Restore
$devicetype = [Microsoft.SqlServer.Management.Smo.DeviceType]::File

$files = gci C:\psbackups

$BackupFiles = @()

foreach ($file in $files){
    $restoredevice = New-Object -TypeName Microsoft.SQLServer.Management.Smo.BackupDeviceItem($file.FullName,$devicetype)

    $restore.Devices.add($restoredevice)
    $errcnt=0
    try{
        $restore.ReadMediaHeader($sqlsvr)
    }
    catch [System.Exception]{
        write-output "$file is not a sql backup file `n"
        $errcnt =1
    }
    finally {
        if ($errcnt -ne 1){
            write-output "$file is a sql backup file `n"
            $BackupFiles += $file
        }
        $errcnt = 0
    }
    $restore.Devices.remove($restoredevice)
    Remove-Variable restoredevice
}

We add the positive matches to a $BackupFiles array so we can refer to them later.

So, what can you do if someone’s running their backups like this:

backup database psdb1 to disk='c:\psbackups\psbd1_backup.bak'
backup log psdb1 to disk='c:\psbackups\psbd1_backup.bak'
backup database psdb1 to disk='c:\psbackups\psbd1_backup.bak'
backup log psdb1 to disk='c:\psbackups\psbd1_backup.bak'

I have seen something similar out in the wild, and they had a BIG backup file for quite a small database, and making sure you restored the version you wanted took a lot of looking up. In this case we use PowerShell’s implement of RESTORE HEADERONLY, ReadBackupHeader:

Import-Module "SQLPS" -DisableNameChecking

$SQLSvr = New-Object -TypeName  Microsoft.SQLServer.Management.Smo.Server("WIN-C0BP65U3D4G")
$Restore = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Restore
$DeviceType = [Microsoft.SqlServer.Management.Smo.DeviceType]::File

$file = gci c:\psbackups\psbd1_backup.bak

$RestoreDevice = New-Object -TypeName Microsoft.SQLServer.Management.Smo.BackupDeviceItem($file.FullName,$devicetype)
$Restore.Devices.Ddd($RestoreDevice)
$Restore.ReadBackupHeader($sqlsvr) | Format-List -Property DatabaseName, BackupStartDate, FirstLSN, BackupTypeDescription, Position
$Restore.Devices.Remove($restoredevice)

Here we pipe the output from ReadBackupHeader through Format-List to extract just the values we want:

DatabaseName          : psdb1
BackupStartDate       : 16/09/2013 07:18:37
FirstLSN              : 40000000049700037
BackupTypeDescription : Database
Position              : 1

DatabaseName          : psdb1
BackupStartDate       : 16/09/2013 07:18:37
FirstLSN              : 40000000049000001
BackupTypeDescription : Transaction Log
Position              : 2

DatabaseName          : psdb1
BackupStartDate       : 16/09/2013 07:18:37
FirstLSN              : 41000000004700037
BackupTypeDescription : Database
Position              : 3

DatabaseName          : psdb1
BackupStartDate       : 16/09/2013 07:18:38
FirstLSN              : 41000000002600001
BackupTypeDescription : Transaction Log
Position              : 4

ReadBackupHeader returns the same information as RESTORE HEADERONLY does.

Not that we have the position of the backups with the file we can restore the one we want by using the FileNumber property:

Import-Module "SQLPS" -DisableNameChecking

$SQLSvr = New-Object -TypeName  Microsoft.SQLServer.Management.Smo.Server("WIN-C0BP65U3D4G")
$Restore = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Restore
$DeviceType = [Microsoft.SqlServer.Management.Smo.DeviceType]::File

$file = gci c:\psbackups\psbd1_backup.bak

$restoredevice = New-Object -TypeName Microsoft.SQLServer.Management.Smo.BackupDeviceItem($file.FullName,$devicetype)
$Restore.Devices.add($restoredevice)
$Restore.DatabaseName = "psdb1"
$Restore.FileNumber = 3
$Restore.SQLRestore($SQLSvr)
$restore.Devices.Remove($restoredevice)

This will restore the most recent full backup from our example

So now we can find valid SQL Server backups on a given filesystem, and then find out what’s contained within them. Tomorrow we’ll look at taking this information, and putting it into a custom object so we can collate the results from multiple files and build up our restore plan.

This post is part of a series posted between 1st September 2013 and 3rd October 2013, an index for the series is available here.