Over the last couple of posts we’ve covered increasingly complex restore scenarios, but we’ve always restored to the most recent point we can. Today we’re going to investigate restoring to a point somewhere else in the backup chain.

To do this, we may have to find:

  • The most recent Full database backup taken prior to the point we want to restore to
  • The most recent differential backup prior to the restore point
  • All of the transaction log backups from the differential up to and including the one covering the point we want to restore to

It is possible that we may only need 1 or more of those. It may be that our Point in Time (PiT) requirement can be purely covered by our full backup, or we may not need the differential as we can reach our PiT with just the full backup and transaction logs.

To give ourselves enough data to work with, we’ll create a simple database:

create database PointInTime;
go

use PointInTime;
go

create table step
(
StepID int identity(1,1),
StepTime datetime
)
go

And then we’ll simulate 7 “days” worth of database usage:

import-module "SQLPS" -DisableNameChecking

$Server = "WIN-C0BP65U3D4G"

$minutes = 0
$hours = 0
$days = 0

Backup-SqlDatabase -ServerInstance $Server -Database PointInTime -BackupAction Database -BackupFile "c:\piit\chain-start.bak"

While ($days -lt 8){
    $hours = 0
    while ($hours -lt 24){
        $minutes = 0
        while ($minutes -lt 60){
            Invoke-Sqlcmd -ServerInstance $Server -query "insert into PointInTime.dbo.Step (steptime) values (current_timestamp)"
            Start-Sleep -Milliseconds 500
            $minutes++
        }
        if ($hours -eq 18){
            if ($days%3 -eq 0){
                #full backup
                $backupfile = "c:\piit\PointInTime_"+$days+"_"+$hours+".bak"
                Backup-SqlDatabase -ServerInstance $Server -Database PointInTime -BackupAction Database -BackupFile $backupfile
            }else{
                #diff backup
                $backupfile = "c:\piit\PointInTime_.bcf"
                Backup-SqlDatabase -ServerInstance $Server -Database PointInTime -BackupAction Database -BackupFile $backupfile -Incremental
            }
        }else{
            #log backup
             $backupfile = "c:\piit\PointInTime_"+$days+"_"+$hours+".trn"
             Backup-SqlDatabase -ServerInstance $Server -Database PointInTime -BackupAction Log -BackupFile $backupfile
        }
        $hours++
    }
    $days++
}

Here we’re simulating 1 “week” of the life in a database, with the following occasions:

  • Every ‘minute’ we insert a row into the steps table
  • Every ‘hour’ we take a transaction log backup
  • At ’18:00′ on every 3rd ‘day’ we take a full backup
  • At ’18:00′ on days where we don’t take a full backup we take a differential backup

The script should take about 90 minutes to complete, and give you 162 backup files. And just to be awkward, and so we can reprove our previous scripts, I’ve put all the differential backups into a single file.

My version of this database runs from 16:13 20/09/2013 till 17:36, so I need to pick a PiT between those boundaries. Again we’re going to assume we’ve saved our file reading functions into a script file, so we can just . include them here:

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

$backupfiles += Get-SQLBackupFiles -FilePath c:\piit\ -SQLServer "RestoreServer"

foreach($file in $BackupFiles | Sort-Object -property LastLSN){
    $backups +=Get-BackupContents -FilePath $file -SQLServer "RestoreServer"
}

$PointInTime = get-date("16:59 20/09/2013")
$filter = get-date($PointInTime) -format "dd MMMM yyyy HH:mm:ss"
$PointInTimeSQL = get-date($PointInTime) -format "MMM dd, yyyy hh:mm tt"
$RestoreFiles = @()

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

$RestoreServer = New-Object -TypeName  Microsoft.SQLServer.Management.Smo.Server("RestoreServer")
$devicetype = [Microsoft.SqlServer.Management.Smo.DeviceType]::File

$RestoreFiles = $RestoreFiles | sort-object -property LastLSN

foreach ($backup in $RestoreFiles ){
    $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.ToPointInTime = $PointInTimeSQL
    $restore.Database = $backup.DatabaseName
         $restore.SqlRestore($RestoreServer)

    $backup | fl filepath, lastlsn, backuptypedescription, backupstartdate
    if ($backup.LastLSN -eq $RestoreFiles[-1].LastLSN){
        $query = "Restore database "+$restore.Database.tostring()+" with recovery"
        Invoke-Sqlcmd -ServerInstance $RestoreServer -query $query
    }
    $restore.Devices.Remove($restoredevice)
}

The first few lines are the familiar ones we’ve been using over the last couple of posts.

We then set some DateTime variables. We need a couple of different formats to match the formats in use later in the script, and I find it easier to keep the formatting seperate to make the later lines more readable.

We then build up a $RestoreFiles array like before. But this time the filtering is different. Now we don’t want the latest Full Backup, we want the latest full backup previous to our Point in Time. And then we want the newest differential in the gap between our Full backup and our Point in Time. Finally, we find all the transaction log backups between our differential and out Point in Time, remembering that we also need the log back that goes past the Point in Time! It can be easy to get the filtering wrong and stop at the log backup before the correct last one.

We sort $RestoreFiles to by LastLSN to ensure that we get the backup files in the correct order. We then loop through every backup in $RestoreFiles, building up a Restore object and restoring it. After each restore we compare the current backup’s LastLSN value with that of the final element in $RestoreFles (the index position -1 in an array will return the last object in the array), if they are the same then we have restored our last file, and now need to recover our database. We do this by using Invoke-SQLCmd to run a RESTORE DATABSE [dbname] WITH RECOVERY.

And if you now run select max(steptime) from PointInTime.dbo.Step you’ll find that you have a recovered database and that the returned value should be within 30 seconds of your chosen point in time.

Tomorrow we’ll look at cutting this up into reusable functions, and also adding some code to enable  our scripts to determine a random point in time covered by our backups, and restore to then for testing.

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