Day 16 of 31 Days of SQL Server Backup and Restore using PowerShell: A more robust transaction restore

Over the last 2 days we’ve looked at getting backup information from 2 sources:

and loading it into a PowerShell object. Today we’re going to put that object to use to show how we can use it to make sure we’re restoring files in the right order without having to rely on filenames or modified dates.

To setup we need a database and some backups. And we’ll make those backups a little complicated just to show off everything.

create database RobustRestore;
go

use robustrestore
go

create table steps(
StepID int id(1,1),
StepTime datetime
)
go
backup database RobustRestore to disk='c:\robust\robustrestore.bak'
go
insert into steps (steptime) values (current_timestamp)
go
backup log RobustRestore to disk='c:\robust\robustrestore.trn'
go
insert into steps (steptime) values (current_timestamp)
go
backup log RobustRestore to disk='c:\robust\robustrestore2.trn'
go
insert into steps (steptime) values (current_timestamp)
go
backup log RobustRestore to disk='c:\robust\robustrestore.trn'
go
insert into steps (steptime) values (current_timestamp)
go
backup log RobustRestore to disk='c:\robust\robustrestore2.trn'
go

We now have a backup chain, and our transaction logs are interleaved across 2 files. Not something you’d want to see in production, but suitably complex for a simple demonstration.

First we use our disk scraping technique to load our object with all the backup information from the files. The function definitions are here – Day 14 of 31 Days of SQL Server Backup and Restore using PowerShell: Reading Backup information into a custom object

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

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

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

Assuming you get no errors, then we now have a $backups object with 5 entries, 1 for each of the backups. So how are we going to use this to run a restore, and make sure we get everything in the right order. Well, let’s take a look at one of the entries:

PS C:\> $backups.item(1)

FilePath               : C:\robust\robustrestore.trn
BackupName             :
BackupDescription      :
BackupType             : 2
ExpirationDate         :
Compressed             : 0
Position               : 1
DeviceType             : 2
UserName               : Server1\Administrator
ServerName             : Server1
DatabaseName           : RobustRestore
DatabaseVersion        : 706
DatabaseCreationDate   : 18/09/2013 09:14:01
BackupSize             : 77824
FirstLSN               : 40000000007600178
LastLSN                : 40000000016300001
CheckpointLSN          : 40000000007600178
DatabaseBackupLSN      : 40000000007600178
BackupStartDate        : 18/09/2013 09:14:02
BackupFinishDate       : 18/09/2013 09:14:02
SortOrder              : 0
CodePage               : 0
UnicodeLocaleId        : 1033
UnicodeComparisonStyle : 196609
CompatibilityLevel     : 110
SoftwareVendorId       : 4608
SoftwareVersionMajor   : 11
SoftwareVersionMinor   : 0
SoftwareVersionBuild   : 2100
MachineName            : Server1
Flags                  : 512
BindingID              : 815b85e5-d051-4afd-a192-bb9fb2f7302c
RecoveryForkID         : df243f5b-a451-427e-8622-ecc7996a390a
Collation              : Latin1_General_CI_AS
FamilyGUID             : df243f5b-a451-427e-8622-ecc7996a390a
HasBulkLoggedData      : False
IsSnapshot             : False
IsReadOnly             : False
IsSingleUser           : False
HasBackupChecksums     : False
IsDamaged              : False
BeginsLogChain         : True
HasIncompleteMetaData  : False
IsForceOffline         : False
IsCopyOnly             : False
FirstRecoveryForkID    : df243f5b-a451-427e-8622-ecc7996a390a
ForkPointLSN           :
RecoveryModel          : FULL
DifferentialBaseLSN    :
DifferentialBaseGUID   :
BackupTypeDescription  : Transaction Log
BackupSetGUID          : 2d5edca4-7ed2-473d-b2ca-124f9cca8784
CompressedBackupSize   : 77824
Containment            : 0

So we can see that we have all the information we could want in there to build up our backups. We can use BackupType or BackupTypeDescription to determine what sort of restore (database or log) we need, we have FilePath to tell us where the file is, Position tells us where in that file the particular backup is and DatabaseName let’s us confirm that we’re restoring a backup from the correct database.

But how do we ensure we’re restoring the backups in the right order? We have 2 options here, Times or LSN. We could order by BackupStartDate or BackupFinishDate, and most times this would be absolutely fine. But if we have backups that take a particularly long time to complete you can end up with backups happening within the window of other backups.

A more reliable method is to use Log Sequence Numbers (LSNs). LSNs are strictly increasing number that SQL Server assigns to every transaction, and this is written in the transaction log when the transaction is committed. LSNs are not contiguous or of any use for arithmetic (ie; subtracting FirstLSN from LastLSN for a Transaction log backup won’t tell you how many transactions the backup contains). What they do guarantee is that if a_LSN is greate than b_LSN then a_LSN was committed to the transaction log AFTER b_LSN. So this makes it ideal for working out the order to restore our files. For more information on LSNs, please see Technet for an Introduction to Log Sequence Numbers

So knowing that, we can now look at out restore script:

$RestoreServer = New-Object -TypeName  Microsoft.SQLServer.Management.Smo.Server("RestoreServer")
$devicetype = [Microsoft.SqlServer.Management.Smo.DeviceType]::File
foreach ($backup in $backups | 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)
}

As you can see, we can now dynamically populate everything from our $Backups object. Except for the SQL Server we want to restore to.

In that example we were assuming that we only have backups from one database, and even just the one SQL Server in the folder we’d scanned. By altering the filtering in the foreach we can ensure we only see the details we want to see. For example, if you wanted to restore the backup of Database “dbprod” from SQL Server “DBServer1” then you’d do the following:

$RestoreServer = New-Object -TypeName  Microsoft.SQLServer.Management.Smo.Server("RestoreServer")
$devicetype = [Microsoft.SqlServer.Management.Smo.DeviceType]::File
foreach ($backup in $backups | Where-Object {($_.DatabaseName -eq "dbprod") -and ($_.ServerName -eq "DBServer1")} | 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)
}

From that it can be seen that we can really leverage PowerShell’s built in cmdlets to extract just the restore information we need from a whole range.

Tomorrow we’ll look at how we can work with multiple days worth of data, and also bring Differential backups into the picture.

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

Tagged ,

Leave a Reply

Your email address will not be published. Required fields are marked *