Limitations of Restore-DbaDatabase

Like all tools, Restore-DbaDatabase isn’t able to do everything that everyone wants it to. Certainly, at the moment I’d like it to write it’s own blog posts and fetch me a cold beer, but that doesn’t happen

A lot of the below isn’t complaining about people asking for features. If we can do it, we will, and we’re keen to make this work for as many people in as many situations as possible

But quite a few requests over the years have been non starters for a number of reasons.

Ye cannae break the laws of SQL Server

Paraprasing Scotty a bit they. The way dbatools is written, we only use SMO and T-SQL. This means we can’t do anything that SQL Server can’t do itself.

While everyone would like to be able to restore a single object from a SQL Server backup (if you would, then please add your voice to this UserVoice request ‘Restore a table from a backup’). Like a lot of people, I can think of ways of doing this, but that involves delving deep into the backup format and lots of low level code, and as I’m not on the product team I’m probably missing something blindingly obvious.

This is one reason why restores on Linux can occasionally be tricky. dbatools uses quite a few of the ‘xp’ eXtended stored Procedures to do things like test for paths or get directory listings. Under the hood, things like xp dirtree are dlls, and they’re compiled for Windows. They don’t exist on *nix SQL Server installs, so we try to work around them but it doesn’t always work out.

We can’t restore databases down a SQL Server versions. So if you’ve got some backups from a SQL Server 2014 edition we can only restore them to SQL Server 2014, 2016 or 2019. There is no mechanism in SQL Server we can use to restore them to SQL Server 2012 or lower for you.  This also means we can’t just jump from SQL Server 2000 to 2012, you’ll have to do the usual intermediate restore first.

Parallelism

We’ve played with making it parallel and it’s a lot more trouble than you’d think. The first constraint is that dbatools is designed and pretty much guaranteed to run on PowerShell v3.0 and upwards. This limits us to the language features that are available to us.

Each users environment is different as well. The setup for one person who’s got OS admin and is running direct on the SQL Instance machine is different for the person who’s running across the network with only SQL Server access

There’s also issues with contention trying to do too much in parallel. You may be able to tune everything for the nightly refresh of dev from prod, but making it generic isn’t so easy. Personally in a DR solution I’d rather have a slower, trusted and proven restore than trying to eke out a bit more performance with the risk of having to restart.

Options that I’ve tried with some success, and some failures are:

We’ll be taking a look at some of these later in the series, just so you can see the pros and cons of using them

Third Party Integrations

We often get people asking if we can add support for Backup Product X to Restore-DbaDatabase. The answer is no. We do have some reasons, so thought I’d go through them here:

Licensing: We run quite an extensive test suite on the code base. Each developer needs to check every command as a tweak in one function can break lots of others, especially in Restore-DbaDatabase which underpins comands like Copy-DbaDatabase and Start-DbaMigration. This would mean every dbatools developer (and our appveyor lab) would need a licensed copy of the software. Given we’re open source, that could be anyone on the planet. Can’t really see that happening

The 3rd Party should be doing the work: If your chosen 3rd part isn’t providing the PowerShell interface you need, then you should be pushing them to implement it. They’ll have the inside knowlege of how it works to hopefully write a proper performant system, rather than us just wrapping DOS commands in PowerShell wrappers

Lack of return on investment: We’re not sure that there’d actually be that much return on investment if we did add them. Our aim has to been to build up support around Microsoft’s own implementation, to help out DBAs who suddenly need some help. The one thing we can count on, is that those DBAs have access to SQL Server, PowerShell and SMO. We don’t want to spend time writing code that may only support 5% of DBAs.

Corrupted files

If a backup file is corrupted then there is nothing we can do. If we could generate perfect backup files on the fly, it’d be a miracle. Backup files need to be stored securely and reliably, if they are missing or corrupted then it’s game over.

Same goes for backups where you’ve lost the encryption key/certificate. If I could crack those I’d probably be making a lot more money, and being a lot quieter about what I get up to!

Restore System Databases

This one comes up regularly. I spent about 3 months trying to get this to work 18 months ago and gave up.

The issue isn’t technical, I could get it to work 90% of the time. But when it went wrong it completely hosed the SQL Server instance and needed manual intervention to bring it back online (you try finding a 2005 msdb at silly o’clock in the morning because you’ve broken the shared test environment).

That was with standalone instances, with SQL Server clusters, success was much lower.

If Restore-DbaDatabase screws up someone’s user land database restore, they are going to be in the same situation as they we before. SQL Server is still running, and they can try again, or come online and ask for help in Slack or Github Issues and a lot of people can help

If Restore-DbaDatabase screws up someone’s system database restore, they are completely stuffed. They’re going to have to manually fix that issue, and it’s not always as easy as it appear. There’s no way we can hope to provide support to people in that situation. If I was doing that for a client I’d want to be remoted in to the box doing it myself, not passing along random commands and having to wait for feedback. While we have plenty of people on the team who could help here, I don’t feel it’s fair for this type of responsibility to fall onto a team of volunteers.

If someday, someone comes up with a 100% rock solid way to implement this that’d be great!

Conclusions

We love extending dbtools, Restore-DbaDatabase included, but there are limitations on reality, time and resources that we have to obey (if someone can come up with a non chemical way to fit 32 hours into a 24 day, let me know please)

If you fancy dropping me some Github sponsorship for coffee, beer or a better home coding environment I’d be very thankfull, Github Sponsor Page

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