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