Recreating Backup History

Yesterday we looked at using BackupHistory from Get-DbaDbBackupHistory to speed up database restores by pulling the information from SQL Server.

However, this isn’t always available. Perhaps when you’ve a hard security split between the production box and test environments, or when you’ve completely lost the original SQL Server instance in a distaster of some sorts

Today we’ll look at how you can save your backup history to disk, or recreate it from backup files ahead of time

Creating BackupHistory from backup files

We’ll look at creating BackupHistory from file first, we can save this in the same way that we’ll do with BackupHistory obtained via Get-DbaDbBackupHistory.

As mentioned before in this series, Restore-DbaDatabase really consists of 4 major components under the hood. Today we’ll be taking a look at one of these, the Get-DbaBackupInformation command.

This is the function that Restore-DbaDatabase uses to scan backup files and build the BackupHistory used internally by the rest of the command. We’re going to run this on it’s own to build up the objects.

Running it is quite simple:

   $backupHistory = Get-DbaBackupInformation -SqlInstance server1\instance -Path \\server\files$\db1

This will scan all the backup files in \\server\files$ using the server1\instance SQL instance, filling $backupHistory with BackupHistory objects. This means we can now do this:

    $backupHistory | Restore-DbaDatabase -SqlInstance server1\instance -DatabaseName db2 -TrustDbBackupHistory

and skip all the scanning of the files inside Restore-DbaDatabase. But what’s the point in that? We’ve had to do it anyway

It’s great when you’re going to have to restore a dabatabase a number of times

Say you’re provisioning a number of SQL Instances for testing:

    $backupHistory = Get-DbaBackupInformation -SqlInstance server1\instance -Path \\server\files$\db1
    $instances = ("server1\instance","server2\dev","server4\test")
    ForEach ($instance in $instances) {
        $backupHistory | Restore-DbaDatabase -SqlInstance $instance -TrustDbBackupHistory  
    }

Will restore the database onto 3 different Sql Server Instances, with only 1 set of file scans, which will cut the time down quite a bit with the lack of file scans.

Another example can be when you’re stepping through a SQL Server restore in time chunks, as in this example of finding out when some data was deleted from the database and by whom where it saves scanning the backup files on each restore.

Or, if you do some preparation then losing the backuphistory in your original SQL Server instance might not be slow you down too much. You could run this code each night:

    Get-DbaBackupInformation -SqlInstance server1\instance -Path \\server\files$\db1 -ExportPath "\\server1\files$\db1history\dbhistory-db1-$(Get-date -format 'yyyyMMddhhmm').xml"

Which reads all the headers and then saves them into a timestamped file for later use. At a later time you can turn that xml file back into BackupHistory and do this:

    $backupHistory = Get-DbaBackupInformation \\server1\files$\db1history -Import
    $backupHistory | Restore-DbaDatabase -SqlInstance server1\instance -DatabaseName db2 -TrustDbBackupHistory -NoRecovery

Which will read all of the export files in the folder, turn them back into BackupHistory ready to be piped into Restore-DbaDatabase for a restore. If you set the inital restores to use NoRecovery, then while those files are restoring you can be scanning the files since the last export:

    $tailBackupHistory = Get-ChildItem \\server1\files$\db1 | Where-Object {$_.DateCreated -gt '15/05/2020 21:30' | Get-DbaBackupInformation -SqlInstance server2\sqlexpress
    --Wait for the other restore to finsih, and then complete with:
    $tailBackupHistory | Restore-DbaDatabase -SqlInstance server1\instance -DatabaseName db2 -TrustDbBackupHistory -Continue

Here we’re using Get-ChildItem to get all the new backup files since the last export, piping them into Get-DbaBackupInformation to read the header, and offloading the header reads to a different SQL Server instance, as server1\instance is busy doing a restore. Once the intial restores are done, you’re ready to complete the restore.

Speeding up the Backup Scans

When we’re using Restore-DbaDatabase we can only use the single SQL Server instance to do the header reads on the backup files. When you’re creating them with Get-DbaBackupInformation you can parcel out the work it you want to. Break you the files into chunks and scan each chunk on a seperate SQL Server instance. You can then import all the xml files into one master object for the main restore run.

This will work with any edition of SQL Server, so if your ream all have SQL Server Express installed on their workstations you can quickly pararllelise the process and be done quicker.

Export the Backup History from SQL Server

Even quicker would be not having to scan the headers at all during a DR scenario. We backup out data, so why not include export the BackupHistory as well?Get-DbaDbBackupHistory is outputting BackupHistory ojects, just like Get-DbaBackupInformation. So each night you can do this:

    Get-DbaDbBackupHistory -SqlInstance server1\instance -Since (Get-Date).Add(-25) | Export-Clixml -Path "\\server1\files$\server1\dbhistory-db1-$((Get-date).AddHours(-25).ToString('yyyyMMddhhmm')).xml" -Depth 5

And you’ve now got the backup history for the server1\instance instance stored away safely. I’ve added a little bit of overlap to make sure that you get every piece of BackupHistory. Restore-DbaDatabase will ignore duplicate history objects if they’re passed into it.

You can’t put it backup into msdb, well you could try but we don’t pull everything and there’s lots of SQL Server constraints you’d have to work around as well.

But, now if you lose the instance (commiserations), you can recreate the backup history for the last 7 days like this:

$backupHistory = Get-ChildItem -Path \\server1\files$\server1\ | Where-Object {$_.LastWriteTime} -get (Get-Date).AddDays(-7) | Import-CliXml

And then restore it like this:

$backupHistory | {$_.DatabaseName -NotIn ('master','model','msdb')} | Restore-DbaDatabase -SqlInstance NewServer -UseDestinationDefaultDirectories -NoRecovery

And your databases will be restoring to their new home while you scan the last few backups you need to continue the restore to the latest point.

Conclusions

Using BackupHistory can really speed up a restore process. So it’s great to have a standby copy just in case the worst happens, but also to know that you can build it again up from backup files if needed.

All posts in this series can be found at 31 Days of dbatools Backup and Restores