Musings of a Data professional

Stuart Moore

Day 8 – Why is Restore-DbaDatabase like it is? – 31 days of dbatools backup and restores

Why is Restore-DbaDatabase like it is?

At first glance Restore-DbaDatabase looks like a slow lumberig complex beast. In reality it’s not that bad.

It’s the result of design decisions I took in wanting a solid versatile command that could cope with everything that people would want from it.

In this post, we’ll go through the main decisions/points of contention one by one

Why scan the headers?

Why can’t we just use:

    $SQLSvr = New-Object -TypeName  Microsoft.SQLServer.Management.Smo.Server -ArgumentList ("Server1")
    $Restore = New-Object -TypeName Microsoft.SQLServer.Management.Smo.Restore
    $DeviceType =[Microsoft.SqlServer.Management.Smo.DeviceType]::File
    $files = gci c:\backupfolder 
    $Restore.Database='psrestore'
    $Restore.NoRecovery= $FALSE

    $files | Sort-Object -Property LastWriteTime | %{
        if ($_ -eq $files[-1]){
            $Restore.NoRecovery= $FALSE
        }

        $BackupDevice = New-Object Microsoft.SqlServer.Management.Smo.BackupDeviceItem -ArgumentList $file.FullName, $DeviceType
        $Restore.Devices.Add($BackupDevice)
        $Restore.SQLRestore($SQLSvr)
        $Restore.Devices.Remove($BackupDevice)
        remove-variable BackupDevice
    }

and be done with it?

It came from the horrors

Well, over the 24 years I’ve been working with SQL Server I’ve come across some horrendous sets of backup files that would never work with that code. The hightlights of the 🤬🤬🤬🤬 I’ve come across include:

  • Files with dates altered
  • Multiple Databases in 1 folder
  • Multiple backups in a single file (they didn’t change the each day)
  • The maintenance plan that wrote everything into 1 backup file, full, diff and log

This is one of the reasons why Restore-DbaDatabase doesn’t trust dates, because in a lot of case you can’t. Yes, it does mean scanning the headers but there are way to improve that or work around it, which we’ll be covering in a later post

I wanted a tool that I could just point at a mass of backup files and leave it to sort out what it needed to restore as many of the databases in there that it could.

Take the backups in this github repo: Awkward Restores

There are 5 database in those files, try and restore them all to the latest point in time.

Running Restore-DbaDatabase across them like this:

    Restore-DbaDatabase -SqlInstance localhost\sqlexpress -Path c:\dbatools\blogtest

Will sort them out and restore to the latest version of each

Please feel free to try any other restore option out there to restore those. Yes, you can manually scan them and build up the restore chain, but why not let Restore-DbaDatabase just do it all for you?

LSNs

This leads on to the next thing people focus on, why so much adherence to LSNs? (LSN -eq Log Sequence Number). Well these are the real arbiter in SQL Server of what happens when. Quickly an LSN is allocated to everything written to the Transaction Log. They mean nothing other than as an ordering mechanism. If LSN A is greater than LSN B then the operation associated with LSN A happened after LSN B, no if, no buts, no arguments.

This also ties into checking things like RecoverForkGUID, we want to make sure that just because the database name is the same that we’re actually restoring along the correct recovery chain

Why multiple internal commands?

I’ve always liked the idea of 1 simple function that does one job well. I think the ship sailed on the ‘simple’ bit early on in Restore-DbaDatabase’s life, but I definitely split it up into multiple functions when I did the last major rewrite in 2018. The reasoning was purely to make it easier to maintain and understand.

This was always going to be a complex beast give what was expected from it, so I’ve tried to keep it as sane as possible.

Why so many options?

Restore-DbaDatabase has a lot of options because that’s what the community has asked for. We wanted to offer all the usual options that T-SQL Restore offered, and everything else can be tracked back to a user request in the Slack Channel or a Github Issue. Very little has been added just for the fun of it (well, maybe some bits, but they’re fun :))

Most of the options don’t impact performance as they’re just skipped unless you’ve asked it to perform them.

Conclusions

Some might say Restore-DbaDatabse is too complex, I like to think it’s just complex to do everything it needs to do. It can be tweaked easily when new edge cases crop up, or when Microsoft release new features, but it’s not so hard that other people can’t just in and fix bits.

It might look complex at first sight, but it’s not really. If you’re getting stuck with a PR for Restore-DbaDatabase, then please just get in touch.

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

Previous

Day 7 – Backup up to Azure – 31 days of dbatools backup and restore

Next

Day 9 – Limitations of Restore-DbaDatabase – 31 days of backup and restores

4 Comments

  1. Garry Bargsley

    Is there a way to dig in an make it choose the latest Recovery Fork or add a parameter to do the same. I have a major problem with Recovery Forks in my environment and causes Get-DbaLastBackup worthless.

    • Stuart Moore

      Yes, there are, There are options for you:

      If you know the recovery fork you want to use you can specify it with the -RecoveryFork parameter. The problem with RecoveryForks is that they’re identified with GUIDs, so there’s nothing to identify which one is ‘newer’

      The simpler method I tend to use is the -Since parameter. This takes a DateTime object, and will only return results from that point in time. Usually for me setting -Since (Get-Date).AddDays(-7) fixes things, as the db is usually under a week old

      The other thing that can help is keeping backup history trimmed down to just what you need, as I cover in this post – Please clean down your SQL Server backup history – as this will reduce the number of Recovery forks stored.

  2. Garry Bargsley

    That is a good idea on the -Since. I did not think of that.

    We do a backup history cleanup at 60 days. I was hoping the Recovery Forks would roll off after full backups happened, but it seems as though they persist.

    Let me work with the -Since and see what I can come up with.

    Thanks for the quick response.

    • Stuart Moore

      Recovery Forks don’t roll off like that. The exist until they age out of the backup history.

      They just indicate that at some point the database restores diverged. So if a database is on Recovery Fork A, and then you restore it, it will now be on Recovery Fork B forever. Unless you restore it again, in which case it will be on Recovery Fork C forever.

      Even if the restore is just to move the database 5 minutes into the past it will create a new Recovery Fork, because you’re rolled the database back, there will be multiple versions of the database for the same LSN. SQL Server can’t cope with that, so assigns the Recovery fork to make dbid, LSN, Recovery fork a unique key for each backup.

Leave a Reply

Your email address will not be published. Required fields are marked *

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

Powered by WordPress & Theme by Anders Norén