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:
- Full Backup
- Transaction Log Backup
- Full Backup
- Transaction Log Backup
- Differential Backup
- Transaction Log Backup
- Differential Backup
- 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.
Leave a Reply