Musings of a Data professional

Stuart Moore

Day 18 – Ola Hallengren maintnance solutions and Restore-DbaDatabase – 31 days of dbatools backup and restores

Restoring Ola Hallengren maintenance solutions backups

At dbatools we’re great fans of Ola Hallengren’s maintenance solution for dealing with backups and index maintenance. We even have a command to install and update his solution for you – Install-DbaMaintenanceSolution

If you’re using Ola’s solution then we can make some assumptions about your backups, which means we can offer a few more options.

If you execute the following:

Restore-DbaDatabase -SqlInstance Server1\instance -Path \\backups\files$\db1 -MaintenanceSolution

The we know you’re pointing us at a backup folder created by Ola’s solution. This means we know already to restore down into the folder. This would be the equivalent of

    Restore-DbaDatabase -SqlInstance Server1\instance -Path \\backups\files$\db1 -DirectoryRecurse

which isn’t too big a saving. But if you’re wanting to skip certain types of restores this can really speed things up:

    Restore-DbaDatabase -SqlInstance Server1\instance -Path \\backups\files$\db1 -MaintenanceSolution -IgnoreLogBackup

which doesn’t look much different to

    Restore-DbaDatabase -SqlInstance Server1\instance -Path \\backups\files$\db1 -DirectoryRecurse -IgnoreLogBackup

But there’s quite a performance improvement from the former over the latter. This comes about because we know that Ola’s solution lays out the folder like this:

Server
   |---Database
      |--- FULL
      |--- DIFF
      |--- LOG

Because of this with the first command we can just skip the log folder! With the second version we still have to scan all the backup headers to be sure which files are full backups, differential backups and log backups. With Ola’s solution we know already. And IgnoreDiffBackup works in the same way

You can of course configure whichever backup tool you use to put your files into the same file structure Ola uses. As long as you’re confident that you only have the right type of backups in each folder it will work.

Conclusions

Ola’s maintenance scripts are great, I use them exclusively at work and shudder when I come across an ancient maintenance place when something crawls out of the woodwork. This little switches can improve your performance if you know where you want to restore to.

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

Previous

Day 17 – dbatools Restore-DbaDatabase and Azure – 31 days of dbatools backup and restores

Next

Day 19 – SQL Server Page restores with Restore-DbaDatabase – 31 days of dbatools backup and restores

1 Comment

  1. Lava

    Stuart, thank you so much for all these great articles, very helpful.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Powered by WordPress & Theme by Anders Norén