Day 15 – dbatools and Backup History – 31 days of dbatools backup and restores

Backup History

Today we’re going to look at what underpins a lot of the work of Restore-DbaDatabase, backup history.

At the basic level it is just what it sounds like, a history of all the backups taken of a SQL Server database. Getting that history and and turning it into a restore plan is a  more complex business.

All the examples in the last 14 days have gotten the required backup history from the backup files passed in to them, either via Get-ChildItem or the Path parameter. When this happens a sub function of Restore-DbaDatabase scans every file passed, if it’s a SQL Server backup file it’s headers are read and the information passed back, if it’s not it’s ignored

The results are returned as an array of BackupHistory objects. BackupHistory is dbatools own internal object for backup history. On Day 2 of this series (Backup-DbaDatabase Basics) we saw what’s contained in a BackupHistory object, but it’s worth repeating here:

There’s a lot of information stored in there. A lot of it is mainly of use for the dbatools functions, but other pieces of information are handy to have access to for planning and monitoring. We created the BackupHistory object to make moving this information between dbatools functions using the pipeline. You’ll see it cropping up a lot betweent Backup-DbaDatabase, Get-DbaDbBackupHistory and Restore-DbaDatabase, and it’s used deep inside things like Copy-DbaDatabase and Start-DbaMigration.

Scanning a large number of SQL Server backup files can take a long time. If you’re taking a full database backup on a Sunday night, a differential every 12 hours and a log backup every 20 minutes, then to restore the database to Thursday at 15:35 you’re going to need to scan at least 280 files. This can take a while even on a fast machine.

Today we’re going to look at getting that information from your SQL Server Instance. Tomorrow we’ll be looking at ways of improving the speed, when all you’ve got is the files

Get-DbaDbBackupHistory

SQL Server also stores backup history. Every time you take a SQL Server backup the details are stored in msdb. This is any backup that uses SQL Server built in backup methods, so includes Backup-DbaDatabase. Some 3rd party backup applications will not be recorded, which is why we have issues supporting them (This was partly covered on 9 – Limitations of Restore-DbaDatabase).

This information is queryable, but takes a bit of piecing together to be useful, so we have Get-DbaDbBackupHistory to do it for us. The simplest usage is to just point it at a database:

    Get-DbaDbBackupHistory -SqlInstance localhost\sql2008r2 -Database StopAt

which returns the following:

SqlInstance       Database Type TotalSize DeviceType Start                   Duration End
-----------       -------- ---- --------- ---------- -----                   -------- ---
JUMPBOX\SQL2008R2 StopAt   Log  73.00 KB  Disk       2020-05-12 11:19:45.000 00:00:00 2020-05-12 11:19:45.000
JUMPBOX\SQL2008R2 StopAt   Log  73.00 KB  Disk       2020-05-12 13:34:44.000 00:00:00 2020-05-12 13:34:44.000
JUMPBOX\SQL2008R2 StopAt   Log  73.00 KB  Disk       2020-05-12 11:18:00.000 00:00:00 2020-05-12 11:18:00.000
JUMPBOX\SQL2008R2 StopAt   Full 1.45 MB   Disk       2020-05-12 11:17:05.000 00:00:00 2020-05-12 11:17:05.000
JUMPBOX\SQL2008R2 StopAt   Log  73.00 KB  Disk       2020-05-12 13:32:09.000 00:00:00 2020-05-12 13:32:09.000
JUMPBOX\SQL2008R2 StopAt   Log  73.00 KB  Disk       2020-05-12 13:30:24.000 00:00:00 2020-05-12 13:30:24.000
JUMPBOX\SQL2008R2 StopAt   Log  73.00 KB  Disk       2020-05-12 11:16:15.000 00:00:00 2020-05-12 11:16:15.000
JUMPBOX\SQL2008R2 StopAt   Log  73.00 KB  Disk       2020-05-12 11:15:25.000 00:00:00 2020-05-12 11:15:25.000
JUMPBOX\SQL2008R2 StopAt   Log  73.00 KB  Disk       2020-05-12 13:29:34.000 00:00:00 2020-05-12 13:29:34.000
JUMPBOX\SQL2008R2 StopAt   Full 1.45 MB   Disk       2020-05-12 13:28:44.000 00:00:00 2020-05-12 13:28:44.000
JUMPBOX\SQL2008R2 StopAt   Full 1.45 MB   Disk       2020-05-12 11:14:35.000 00:00:00 2020-05-12 11:14:35.000

I’ve asked for all of the backup history for the StopAt database on localhost\sqlexpress, and it’s been returned. Each row is BackupHistory object, but we only show the most relevant details. Here you can see what type of backups were taken and when there were taken. They’ve come out in a random order, but as this is PowerShell, we can soon fix that:

    Get-DbaDbBackupHistory -SqlInstance localhost\sql2008r2 -Database StopAt | Sort-Object -Property Start

which returns the following:

SqlInstance       Database Type TotalSize DeviceType Start                   Duration End
-----------       -------- ---- --------- ---------- -----                   -------- ---
JUMPBOX\SQL2008R2 StopAt   Full 1.45 MB   Disk       2020-05-12 11:14:35.000 00:00:00 2020-05-12 11:14:35.000
JUMPBOX\SQL2008R2 StopAt   Log  73.00 KB  Disk       2020-05-12 11:15:25.000 00:00:00 2020-05-12 11:15:25.000
JUMPBOX\SQL2008R2 StopAt   Log  73.00 KB  Disk       2020-05-12 11:16:15.000 00:00:00 2020-05-12 11:16:15.000
JUMPBOX\SQL2008R2 StopAt   Full 1.45 MB   Disk       2020-05-12 11:17:05.000 00:00:00 2020-05-12 11:17:05.000
JUMPBOX\SQL2008R2 StopAt   Log  73.00 KB  Disk       2020-05-12 11:18:00.000 00:00:00 2020-05-12 11:18:00.000
JUMPBOX\SQL2008R2 StopAt   Log  73.00 KB  Disk       2020-05-12 11:19:45.000 00:00:00 2020-05-12 11:19:45.000
JUMPBOX\SQL2008R2 StopAt   Full 1.45 MB   Disk       2020-05-12 13:28:44.000 00:00:00 2020-05-12 13:28:44.000
JUMPBOX\SQL2008R2 StopAt   Log  73.00 KB  Disk       2020-05-12 13:29:34.000 00:00:00 2020-05-12 13:29:34.000
JUMPBOX\SQL2008R2 StopAt   Log  73.00 KB  Disk       2020-05-12 13:30:24.000 00:00:00 2020-05-12 13:30:24.000
JUMPBOX\SQL2008R2 StopAt   Log  73.00 KB  Disk       2020-05-12 13:32:09.000 00:00:00 2020-05-12 13:32:09.000
JUMPBOX\SQL2008R2 StopAt   Log  73.00 KB  Disk       2020-05-12 13:34:44.000 00:00:00 2020-05-12 13:34:44.000

That’s nice and all, but what if you’re not looking at a test database and you have a LOT of backuphistory? Well, first think if you need to clear you backuphistory down a bit, but then you can use some of the filters to trim down the results.

The most common one to use is the Last switch

    Get-DbaDbBackupHistory -SqlInstance localhost\sql2008r2 -Database StopAt -Last

which returns the following:

SqlInstance       Database Type TotalSize DeviceType Start                   Duration End
-----------       -------- ---- --------- ---------- -----                   -------- ---
JUMPBOX\SQL2008R2 StopAt   Full 1.45 MB   Disk       2020-05-12 13:28:44.000 00:00:00 2020-05-12 13:28:44.000
JUMPBOX\SQL2008R2 StopAt   Log  73.00 KB  Disk       2020-05-12 13:29:34.000 00:00:00 2020-05-12 13:29:34.000
JUMPBOX\SQL2008R2 StopAt   Log  73.00 KB  Disk       2020-05-12 13:30:24.000 00:00:00 2020-05-12 13:30:24.000
JUMPBOX\SQL2008R2 StopAt   Log  73.00 KB  Disk       2020-05-12 13:32:09.000 00:00:00 2020-05-12 13:32:09.000
JUMPBOX\SQL2008R2 StopAt   Log  73.00 KB  Disk       2020-05-12 13:34:44.000 00:00:00 2020-05-12 13:34:44.000

This is a filtered list of backups to restore the StopAt database to the latest point in time. Other filters include LastFull, LastDiff, LastLog.

You can also run it against multiple databases, if you don’t specify a database it will return results for every database on the instance:

    Get-DbaDbBackupHistory -SqlInstance localhost\sql2008r2 -LastFull

which returns the following:

SqlInstance       Database   Type TotalSize DeviceType Start                   Duration End
-----------       --------   ---- --------- ---------- -----                   -------- ---
JUMPBOX\SQL2008R2 master     Full 3.01 MB   Disk       2020-05-14 16:08:10.000 00:00:00 2020-05-14 16:08:10.000
JUMPBOX\SQL2008R2 model      Full 1.45 MB   Disk       2020-05-14 16:08:11.000 00:00:00 2020-05-14 16:08:11.000
JUMPBOX\SQL2008R2 msdb       Full 15.07 MB  Disk       2020-05-14 16:08:12.000 00:00:01 2020-05-14 16:08:13.000
JUMPBOX\SQL2008R2 StopAt     Full 1.45 MB   Disk       2020-05-12 13:28:44.000 00:00:00 2020-05-12 13:28:44.000
JUMPBOX\SQL2008R2 StripeTest Full 1.51 MB   Disk       2020-05-14 16:08:14.000 00:00:01 2020-05-14 16:08:15.000

But how will this speed up restores?

The big pay off for having this information is that we can do this:

    Get-DbaDbBackupHistory -SqlInstance localhost\sql2008r2 -Database StopAt -LastFull | Restore-DbaDatabase -SqlInstance server1\instance -TrustDbBackupHistory

Now, rather than scanning a lot of file headers we ask the localhost\sql2008r2 instance to give us the backup history, and then pipe it into Restore-DbaDatabase. The real secret sauce is the TrustDbBackupHistory switch on Restore-DbaDatabase, this tells the function to trust the incoming objects. No headers will be read, all the function will do is to check the file exists and is readable by the restoring instance.

Think just how much faster this would be in our earlier example. Reading the headers from 280+ files or a quick SQL query, I know which one I reckon will finish first.

All of the normal Restore-DbaDatabase options are still available to you, so there is no downside to doing restores like this, just a performance improvement.

If you remember I said earlier that Backup-DbaDatabase also outputs a BackupHistory object? Well that means if you want a really budget Copy-DbaDatabase lookalike, the you can do this:

Backup-DbaDatabase -SqlInstance localhost\sql2008r2 -Database StopAt -Path \\server\files$ | Restore-DbaDatabase -DatabaseName StopAt2 -ReplaceDbNameInFile -TrustDbBackupHistory

I’d recommend you stick with Copy-DbaDatabase as it’s got a lot more features, but shows that the BackupHistory object can make a lot of restore usage a lot easier (both for us and for you).

Conclusions

Using Get-DbaDbBackupHistory to get Backup history can really improve the performance of your restores over scanning the file headers.

Tomorrow we’ll look at how to generate backup history from backup files, just in case you’re mislaid your SQL Server instance

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