A quick tip to anyone doing a large restore of SQL Server databases – check the two editions BEFORE starting. By edition, I don’t mean 2012 -> 2014, I mean Enterprise Edition-> Whatever
I recently had to backup, transfer then restore a 300GB database. Checked the versions – 2008-> 2008 no problem. So off it went.
An hour and a half passes and then I get:
Database ‘<Database Name>’ cannot be started in this edition of SQL Server because it contains a partition function ‘AuditPFN’. Only Enterprise edition of SQL Server supports partitioning.
Can’t really argue with the facts there. Partitioning is indeed an Enterprise Edition only feature and I was indeed trying to restore to a Standard Edition install. I’m normally pretty good about checking my assumptions before starting but must have been having a senior moment today.
My only real beef with the error message, and the reason for this post is to warn people that you have to wait for the restore to fully complete before it will tell you that it was never going to work. This is much more annoying when restoring larger databases.
In my case it isn’t a big deal, just some wasted time. I’ll just have to install Developer Edition on my machine, which is something I should have done originally anyway.
The scenario that is a bit scarier is when you are doing a huge restore for real – in full on crisis mode. In this case – make absolutely certain that you are restoring to an appropriate edition before starting. In an emergency you won’t have an additional X number of hours to discover your restore was never going to work in the first place.