Yesterday we looked at restoring our SQL Server database to a point in time, but we had to tell PowerShell and SQL Server when that point should be. As the overall idea of this series is to show you how you can automate your restores, having to manually enter a point in time isn’t really going to cut it. So today we’ll look at how you could pick a point in time from the period covered by your backups. We’ll also look at cutting some of yesterdays code into reusable functions (Remember the PowerShell mantra “Write functions, not scripts”) that we can add to our existing functions.

First we’ll create a funtion to return the subset of backup files we want. To save duplication, we’ll make it work for both point in time restores, and for returning the files needed to restore to the most recent state. We’ll pass in our collection of backup files, a SQL Server name (this being the server name that the backup was performed against), a database name, and an optional point in time.

function Get-BackupsByDate
{param($BackupFiles, [string]$SQLServer, [string]$DatabaseName, [datetime]$PointInTime=(Get-Date).AddDays(1))

    $filter = get-date($PointInTime2) -format "dd MMMM yyyy HH:mm:ss"
    $RestoreFiles = @()
    $RestoreFiles += $BackupFiles | Where-Object {($_.BackupTypeDescription -eq "Database") -and ($_.BackupStartDate -lt $filter) -and ($_.DatabaseName -eq $DatabaseName) -and ($_.ServerName -eq $ServerName)} | Sort-Object LastLSN -Descending | Select-Object -First 1
    $RestoreFiles += $BackupFiles | Where-Object {($_.BackupTypeDescription -eq "Database Differential") -and ($_.BackupStartDate -lt $filter) -and ($_.DatabaseName -eq $DatabaseName) -and ($_.ServerName -eq $ServerName)} | Sort-Object LastLSN -Descending | Select-Object -First 1
    $tmpLSN = $RestoreFiles | Measure-Object -Property LastLSN -Maximum
    $RestoreFiles += $BackupFiles | Where-Object {($_.LastLSN -ge $tmpLSN.Maximum) -and ($_.BackupTypeDescription -eq "Transaction Log") -and ($_.BackupStartDate -lt $filter) -and ($_.DatabaseName -eq $DatabaseName) -and ($_.ServerName -eq $ServerName)}
    $RestoreFiles = $RestoreFiles | Sort-Object -Property LastLSN
    return $RestoreFiles
}

If we don’t get a point on time, we assume that the user wants to restore the to the most current state, so we set a time 1 day in the future and pick the files in the normal method. We also sort the array within the function.

We can then also convert the restore portion of our script into a function that takes the array of files to restore, and a SQLServer to restore them on to:

function Restore-SQLDatabasePointInTime
{param($BackupFiles, [string]$SQLServer, [datetime]$PointInTime=(Get-Date).AddDays(1))

    $restore = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Restore
    $restore.ToPointInTime = get-date($PointInTime) -format "MMM dd, yyyy hh:mm tt"

    $devicetype = [Microsoft.SqlServer.Management.Smo.DeviceType]::File
    foreach ($backup in $BackupFiles ){
        $restoredevice = New-Object -TypeName Microsoft.SQLServer.Management.Smo.BackupDeviceItem($backup.filepath,$devicetype)

        $restore.devices.add($restoredevice)
        $restore.FileNumber = $backup.position
        $restore.Database = $backup.DatabaseName
        $restore.SqlRestore($RestoreServer)

        if ($backup.LastLSN -eq $BackupFiles[-1].LastLSN){
            $query = "Restore database "+$restore.Database.tostring()+" with recovery"
            Invoke-Sqlcmd -ServerInstance $RestoreServer -query $query
        }
        $restore.Devices.Remove($restoredevice)
    }

}

We use the same method to cope with a missing $PointInTime parameter. This will work fine if you pass in a filtered list of backups, it will just restore to the END of the last transaction log rather than stopping at any point in time.

So now we can cut down yesterday’s script to:

$backupfiles = @()
$backups = @()
$restoreFiles = @()
$BackupServer = "Server1"
$RestoreServer = "RestoreServer1"
$DatabaseName = "PointInTime"

$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")

$RestoreFiles = Get-BackupsByDate -BackupFiles $Backups -SQLServer $BackupServer -DatabaseName $DatabaseName -PointInTime $PointInTime

Restore-SQLDatabasePointInTime -BackupFiles $RestoreFiles -SQLServer $RestoreServer -PointInTime $PointInTime

Which is a lot cleaner. But we still have a manually entered Point In time, so how do we get rid of that?

Well, we’ll add another function that filters the backups down to all the backups for a specific database and Servername (just in case you’ve got multiple copies of ‘Accounting’ on different servers). Then we can find the maximum and minimum dates covered by those backups, pick a random number in the middle and convert it to a suitable date:

function Get-DatabasePointInTime
{param($Backupfiles, [string]$SQLServer, [string]$DatabaseName)

    $BackupFiles = $Backupfiles | Where-Object {($_.DatabaseName -eq $DatabaseName) -and ($_.ServerName -eq $ServerName)}
    $MinDate = ($Backupfiles | Measure-Object -Property BackupStartDate -Minimum).Minimum
    $MaxDate = ($Backupfiles | Measure-Object -Property BackupStartDate -Maximum).Maximum
    $seconds =  Get-Random ($MaxDate - $MinDate).totalseconds
    $PointInTime = $MinDate.AddSeconds($seconds)
    return $PointInTime
}

which reduces our script to:

$backupfiles = @()
$backups = @()
$restoreFiles = @()
$BackupServer = "Server1"
$RestoreServer = "RestoreServer1"
$DatabaseName = "PointInTime"

$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-DatabasePointInTime -BackupFiles $backups -SQLServer $RestoreServer -DatabaseName $DatabaseName

$RestoreFiles = Get-BackupsByDate -BackupFiles $Backups -SQLServer $BackupServer -DatabaseName $DatabaseName -PointInTime $PointInTime

Restore-SQLDatabasePointInTime -BackupFiles $RestoreFiles -SQLServer $RestoreServer -PointInTime $PointInTime

By changing the start of the script we can allow the script to take in parameters:


if ($args.Length -lt 3){
     $x =  @"
"
     Wrong usage. Should be : .\PITRestore.ps1 `"`" `"`" `"`"
      - Name of the SQL Server doing the restore
      - Name of the SQL Server where the backup was performed
      - Database to Restore
      - Path to backup files
"@
     write-host $x
}
$RestoreServer = $args[0]
$BackupServer = $args[1]
$DatabaseName = $args[2]
$FilePath = $args[3]

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

$backupfiles += Get-SQLBackupFiles -FilePath $filepath -SQLServer $RestoreServer

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

$PointInTime = Get-DatabasePointInTime -BackupFiles $backups -SQLServer $RestoreServer -DatabaseName $DatabaseName

$RestoreFiles = Get-BackupsByDate -BackupFiles $Backups -SQLServer $BackupServer -DatabaseName $DatabaseName -PointInTime $PointInTime

Restore-SQLDatabasePointInTime -BackupFiles $RestoreFiles -SQLServer $RestoreServer -PointInTime $PointInTime

And we can now call that as:

.\PointInTimeRestore.ps1 "Server1" "PointInTime" "c:\pitt\"

Which is simple to schedule from Tack Scheduler (See Day 10).

So you can now easily schedule a restore of a database to a random point in time. This leads on to the next question of how can we verify that that backup is sound? We’ll look at how we can automate verification of the new database with PowerShell tomorrow.

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