Successful Downgrade of a Dynamics CRM 2011 Database from SQL 2012 to SQL 2008 R2

DISCLAIMER: I do not take any responsibility for any damage done by using this process.  I do not think it would be considered "supported" by Microsoft either.  This post is for education use only.  My scenario was  creating an organization for UAT only.  The real production org during our migration is not going to go through this process.

Let me start by saying that this is not a normal scenario.  Usually you are only going the other way.  But we took a copy of a client org out of a SQL 2008 R2 environment to do some work on it and then we needed to re-upload it to their instance as a UAT org and to test a process by which we were going to merge their two client organizations.

What we missed in the process was that we did our work in our sandbox environment and, in the process, upgraded the organization database to SQL 2012.  This presented a problem when trying to re-attach the org in their environment.

I first tried to use the copy database functionality and even export data functionality in SQL Server Management Studio as I found on some blogs to try to "downgrade" the database.  This did not work, I got errors using both methodologies for various reasons.

I decided to try one last thing and script out the database entirely in version 2008 R2 scripts and push it into the environment and see if Deployment Manager would accept it.

First, in the SQL 2008 environment I created a shell database with the same name to script everything into.
Then in the source SQL 2012 environment I right clicked on the database - clicked "Tasks" - and clicked "Generate Scripts".

The settings for the scripts are below:

1. choose to script the entire database and all objects

2. Here are the base options, click the "Advanced"  button to set the advanced options you will need.

3. Match the options from the next three screen shots

4. Now generate your script file.

5. If this file is big it might not open and run in Sql Server Management Studio.  If this is the case, use the command line tool sqlcmd.exe  to run the query on the target machine.  I had to do this with mine because my script file was over 900 mb.  If you need help with sqlcmd.exe syntax, use this MSDN article: http://msdn.microsoft.com/en-us/library/ms162773(v=sql.105).aspx

IMPORTANT: Specify an output text file when you run it with the -o option.  When you run the script you will see some errors at the top of the output file because it will try to script some security objects, this is ok, there should be no expectations that it would successfully be able to find these security objects in the target system, like AD groups by GUID and such.   Eventually you will start seeing the rows affected lines (usually 1 row at a time) being inserted.  This will take hours.  For a 950mb script file it took me about 3 hours to run.

When it was all done there will probably be a warning at the end of the file (not an error) about the large indexes CRM uses in some cases).  This is normal and can be ignored.

6.  Rename your org database (if desired), and import it via normal mechanisms in deployment manager.

For me it took the org as expected and it functions normally.

- I hope this helps!

DISCLAIMER: I do not take any responsibility for any damage done by using this process.  I do not think it would be considered "supported" by Microsoft either.  This post is for education use only.  My scenario was  creating an organization for UAT only.  The real production org during our migration is not going to go through this process.