With all the new licensing considerations in SQL 2012, everyone's busy trying to figure out exactly what the cheapest license they can get by with is. For one of our instances, we decided rather than Enterprise, we could run the Business Intelligence edition to save some cash. That upgrade path is explicitly listed in the Supported Version and Edition Upgrades documentation.
We clicked through the upgrade wizard as usual and it got through about 85% and then prompted an error:
Wait on the Database Engine recovery handle failed. Check the SQL Server error log for potential causes.
We clicked ok, then it finished and the results showed that the SQL engine and a few other features failed the upgrade. Great. Off to the event logs:
Then take a look at the error log and we have this at the end:
2012-06-12 14:25:16.74 spid5s Error: 926, Severity: 14, State: 1. 2012-06-12 14:25:16.74 spid5s Database 'sysutility_mdw' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog or more information. 2012-06-12 14:25:16.74 spid5s Error: 912, Severity: 21, State: 2. 2012-06-12 14:25:16.74 spid5s Script level upgrade for database 'master' failed because upgrade step 'msdb110_upgrade.sql' encountered error 926, state 1, severity 14. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the 'master' database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion. 2012-06-12 14:25:16.77 spid5s Error: 3417, Severity: 21, State: 3. 2012-06-12 14:25:16.77 spid5s Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.
Well - now you're probably off searching for error fragments and rebuilding your master database, etc. We'd suggest you scroll up a little further and figure out why that sysutility_mdw db couldn't be opened. That's the actual error, the rest of the error is the standard pile of garbage it spits out when a database in script upgrade mode (master) has the script crash - basically, ignore that, it's worthless.
Now we scroll up to the beginning where it tried to bring up that database it couldn't open (sysutility_mdw):
2012-06-12 14:24:35.08 spid21s Error: 909, Severity: 21, State: 1. 2012-06-12 14:24:35.08 spid21s Database 'sysutility_mdw' cannot be started in this edition of SQL Server because part or all of object 'sysutility_ucp_smo_properties_internal' is enabled with data compression or vardecimal storage format. Data compression and vardecimal storage format are only supported on SQL Server Enterprise Edition. 2012-06-12 14:24:35.09 spid21s Error: 905, Severity: 21, State: 1. 2012-06-12 14:24:35.09 spid21s Database 'sysutility_mdw' cannot be started in this edition of SQL Server because it contains a partition function 'sysutility_ucp_aggregation_type_partition_function'. Only Enterprise edition of SQL Server supports partitioning. 2012-06-12 14:24:35.10 spid21s Error: 933, Severity: 21, State: 1. 2012-06-12 14:24:35.10 spid21s Database 'sysutility_mdw' cannot be started because some of the database functionality is not available in the current edition of SQL Server.
Oh dear. What happened is we're smack in the middle of an upgrade (the MDF is actually marked as a 2012 db at this point), but the engine won't attach it because it's using an Enterprise Only feature (actually it's using a few of them). Further, your instance is stuck in that terrible script upgrade mode and won't come out of it until it can finish its work. What's the issue here? Well, either it made a list of the databases that it upgraded earlier and is iterating over them, or the script isn't checking to see if each db is online before trying to run against it. Doesn't really matter, there's only one quick fix here:
- Copy all of the data and log files for that database on to a server with SQL 2012 Enterprise (or probably Developer) Edition installed
- Attach the database
- Remove the unsupported features
- Detach the database
- Copy the data and log files back to their original location on the Business Intelligence Edition server
- Start the SQL service again
We were a little surprised that the upgrade wizard didn't flag that database as unsupported in the GUI portion of the install - It does a few thousand checks already, seems they just forgot a few important ones that only come in to play now that they're turning off commonly used features.