Downgrading from SQL Server 2008 R2 Enterprise to SQL Server 2008 R2 Standard

SharePoint

I recently had to downgrade from SQL Server 2008 R2 Enterprise down to Standard with SharePoint 2010 Enterprise installed. It turned out to be surprisingly easy, thanks to this thread. One issue will likely come up. Search won’t work and you’ll get this error:

Database ‘[name]‘ cannot be started in this edition of SQL Server because part or all of object ‘MSSDocSdids’ is enabled with data compression or vardecimal storage format. Data compression and vardecimal storage format are only supported on SQL Server Enterprise Edition.

Unfortunately you’ll have to recreate search. This post references any issues with that. The same might be true for your Web Analytics Service Application with an error like this:

EVENT 5586: Database ‘WebAnalyticsDatabaseName’ cannot be started in this edition of SQL Server because it contains a partition function ‘DateIdPF’. Only Enterprise edition of SQL Server supports partitioning.

Nevertheless, here are the steps that worked for me. Be sure to follow them to the letter.

  1. Take a good backup of all of your databases (system and user).
  2. Run SELECT @@VERSION and note the specific build number of SQL Server that you are currently on.
  3. Shut down your existing instance of SQL Server.
  4. Copy the master, model, and msdb database files (both mdf and ldf), don’t move them copy them, from the current location to a new folder that you mark as readonly.
  5. Uninstall SQL Server from the system.
  6. Reboot the server.
  7. Install SQL Server Standard Edition.
  8. Apply the necessary Service Pack and/or Cumulative Updates to bring the instance up to your previous build number.
  9. Shutdown SQL Server.
  10. Copy the master, model, and msdb database files (both mdf and ldf) from the folder you saved them in to the correct location for the new install and remove the readonly flag from the files, and change the file ACL’s so that the SQL Service account has Full Control over the files.
  11. Startup SQL Server and if you did it correctly it will startup and be exactly where you were before you made any changes, with all of your user databaes online and you should be ready to let applications connect and resume operations.

The whole process took less than two hours. Good stuff.