Skip to content

master database restore failed in SQL Server 2008 R2 SP1 CU1

by on September 1, 2011

We just woke up having 30+ instances of SQL Server 2008 R2 Enterprise edition in development and testing. Which is bad for obvious reasons. So basically I have to uninstall the Enterprise and re-install Developer Edition on all the machines.

Steps to take:

  • Create a maintenance plan to back up all the databases (simplest way; I create the plan, no schedule, just execute it)
  • Uninstall the Enterprise Edition (10.50.1600, 10.50.2500, etc – plain English: RTM, SP1, etc)
  • Install Developer Edition (10.50.2769 – plain English: SP1 CU1 – I have slipstreamed both SP1 and CU1 in the install media)
  • Restore master (as of documented here)
  • Restore msdb
  • Launch SSMS and clean all the crap that’s left behind

All is good until I restore the master database and get the following error (see print screen below):

This basically tells you that you cannot restore the master database from a lower version (the RTM) to a higher version (SQL Server 2008 R2 SP1 CU1). When this happens, chances are that you had some incorrect entries at the metadata level – in my case, I had some bogus DBs, which although appeared in SSMS, they were not physically present (I only saw this when it was too late).

Now for the crazy hack: restore the master database under a different name in SSMS (see below):

Leave the defaults under Options. After the backup completes successfully, and trust me, it will, stop all the SQL related services (do me a favor and do it from SQL Server configuration manager).

Now go to <<drive>>:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA. Rename the master database files to something else. Do the same for the newly created database, but this time rename them so they can replace the old master database. See below:

In my example, I renamed testmaster database to be master.mdf and mastlog.ldf. the old master database is master.aaa and mastlog.bbb. as you can see, I did the same for msdb, as I needed the jobs, job history, and so on. That’s it, you now have a database that will reflect correctly what you had before.

Final step, cleaning the crappy leftovers: in my case, the only thing that I had to do was to fix Database Mail; for this, I did the following:

alter
database msdb

set
new_broker
with
rollback
immediate

And it worked.

It’s a crazy and stupid hack, but yet again, when everything else fails, you need to try desperate measures.

As a conclusion, before doing any kind of migration, I urge you to make sure you have a usable server that can be migrated. The few extra minutes will pay for themselves big time at a later time.

Best regards,
io

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: