Building up yesterdays technique for ensuring we restore the correct backup files in the the correct order, today we’re going to look at what happens when you bring differentials into the mix, and maybe have more than one day’s backups for your target database.

Like yesterday, we’ll build a database to use as a demonstration:

create database DiffRestore;
go

use DiffRestore
go

create table steps(
StepID int identity(1,1),
StepTime datetime
)
go
backup database DiffRestore to disk='c:\DiffRestore\DiffRestore.bak'
go
insert into steps (steptime) values (current_timestamp)
go
backup log DiffRestore to disk='c:\DiffRestore\DiffRestore.trn'
go
insert into steps (steptime) values (current_timestamp)
go
backup database DiffRestore to disk='c:\DiffRestore\DiffRestore.bak'
go
insert into steps (steptime) values (current_timestamp)
go
backup log DiffRestore to disk='c:\DiffRestore\DiffRestore2.trn'
go
insert into steps (steptime) values (current_timestamp)
go
backup database DiffRestore to disk='c:\DiffRestore\DiffRestore.bak' with differential
go
insert into steps (steptime) values (current_timestamp)
go
backup log DiffRestore to disk='c:\DiffRestore\DiffRestore.trn'
go
insert into steps (steptime) values (current_timestamp)
go
backup database DiffRestore to disk='c:\DiffRestore\DiffRestore.bak' with differential
go
insert into steps (steptime) values (current_timestamp)
go
backup log DiffRestore to disk='c:\DiffRestore\DiffRestore.trn'
go

So we have a simple database, but with a more complicated backup structure. we have in this order:

  1. Full Backup
  2. Transaction Log Backup
  3. Full Backup
  4. Transaction Log Backup
  5. Differential Backup
  6. Transaction Log Backup
  7. Differential Backup
  8. Transaction Log Backup

So to restore this database to it’s latest state we need to restore Backup 4, then Backup 7 (Differential backups are cumulative, so we only need to restore the latest one) and then any following transaction log backups. In this case that’s just Backup 7.

In this case we’ll extract the backups we require, and build a new object to loop through for the actual restore: We’ll assume that the functions we created on Day 14 and Day 15 have been stored in the file SQLBackupFunctions.ps1

Import-Module sqlps -DisableNameChecking
."c:\PSScripts\SQLBackupFunctions.ps1"

$backupfiles = @()
$backups = @()

$backupfiles += Get-SQLBackupFiles -FilePath c:\DiffRestore -SQLServer "Server1"

foreach($file in $BackupFiles){
    $backups +=Get-BackupContents -FilePath $file -SQLServer "Server1"
}

$RestoreFiles = @()

$RestoreFiles += $Backups | Where-Object {$_.BackupTypeDescription -eq "Database"} | Sort-Object LastLSN | Select-Object -First 1
$RestoreFiles += $Backups | Where-Object {$_.BackupTypeDescription -eq "Database Differential"} | Sort-Object LastLSN | Select-Object -First 1
$tmpLSN = $RestoreFiles | Measure-Object -Property LastLSN -Maximum
$RestoreFiles += $Backups | Where-Object {($_.LastLSN -ge $tmpLSNMaximum) -and ($_.BackupTypeDescription -eq "Transaction Log")}

$RestoreServer = New-Object -TypeName  Microsoft.SQLServer.Management.Smo.Server("RestoreServer")
$devicetype = [Microsoft.SqlServer.Management.Smo.DeviceType]::File
foreach ($backup in $RestoreFiles | sort-object LastLSN){
    $restore = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Restore
    $restoredevice = New-Object -TypeName Microsoft.SQLServer.Management.Smo.BackupDeviceItem($backup.filepath,$devicetype)
    $restore.devices.add($restoredevice)
    $restore.FileNumber = $backup.position
    $restore.Database = $backup.DatabaseName
    if ($backup.LastLSN -eq $backups[-1].LastLSN){
        $restore.NoRecovery = $False
    }else{
        $restore.NoRecovery = $True
    }
    $restore.SqlRestore($RestoreServer)

    $restore.Devices.Remove($restoredevice)
}

We do our normal scan of the filesystem to find out backup files and read their headers into our $Backups array. Then, as we know we want to restore to the latest point we can pick the Full backup and the Differential Backup with the highest LSN. We then get all Transaction Log backups with a greater LastLSN than the Differential Backup. All of these are added to an array $RestoreFiles, which we then step through restoring each file populating the required values from our array.

So far we’ve only looked at restoring onto servers where we’re assuming that our database doesn’t already exist. Tomorrow we’ll look at we can easily redirect all the files in a restored database if needed

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