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.
rule30 
Is there a way to detect the version/edition of SQL Server that took the backup? RESTORE HEADERONLY and RESTORE FILELISTONLY don’t appear to make the edition visible
Hi Marc
I’m not aware that being available via RESTORE HEADER ONLY for example. HEADERONLY seems to be able to tell you the version that was used and the actual build number, but not the edition.
The fact that the restore lets you get all the way to the end before telling you that it didn’t work strongly suggests that the edition information isn’t available up front via the backup metadata. If it is available and the MS programmer that implemented recovery just ignores it and then tells you of the problem at the end – then that would be a bit mad. I would almost certainly give that developer a very stern look 😉
Cheers
If the database does not use any of the specific edition features (partitions, compression, TDE, etc.), it can be restored on on a lower edition instance.
To identify any specific edition features in the source database run the following query in the database:
select * from sys.dm_db_persisted_sku_features
http://msdn.microsoft.com/en-us/library/cc280724.aspx