How to restore a SQL Server 2012 database to SQL Server 2008 R2?
I am trying to restore the backup taken from a SQL Server 2012 to SQL Server 2008 R2, and it giving an error
Specified cast is not valid. (SqlManagerUI)
If you have any solution to this please give comment
thanks.
Answer
NOTE, SOME OF THE ABOVE ANSWERS ARE MASSIVELY OUT-OF-DATE! THIS CAN BE DONE AND ALL WITHIN SQL SERVER MANAGEMENT STUDIO (SQL MS)
There are numerous methods you can adopt to "downgrade" a database, but one I have found recently and that I believe was not found in early releases of SQL MS 2012, is the Copy Database Wizard. Here is how you can copy a database from a 2012 server instance to a 2008 R2 instance:
In the 2012 instance, right click on the database you want to copy/"downgrade" and select "Tasks" > "Copy Database...".
"Welcome to the Copy Database Wizard" click [Next].
"Select a Source Server": Set the "Source server" as the 2012 instance (or the higher version server instance), and set the appropriate authentication. click [Next]. [Note. the Server Agent services must be running]
"Select a Destination Server:" Set the "Destination server" as the 2008 R2 (or lower version instance), and set the appropriate authentication. click [Next]. [Note. the Server Agent services must be running]
"Select the Transfer Method" For the sake of this example, select "Use the SQL Management Object method", click [Next].
Select to move or copy the required databases, click [Next].
Configure the destination database path and logical names etc. Select the required option for if the database exists. Click [Next].
Configure the integration services package, click [Next].
For this example, select the "Run Immediately" option for "Schedule the Package" options, click [Next].
"Complete the Wizard", click [Finish] to execute the package and create the "downgraded" database.
You are done, happy days. :]
Another method I found was the SQL Database Migration Wizard which was created by Microsoft and which I think (I don't know) that the wizard above was created from. Get it here http://sqlazuremw.codeplex.com/. To use this package to migrate a databases from SQL Server 20012 to 2008 R2, you can do the following:
Note. Microsoft have now removed SQLAzureMW from Codeplex. I have personally made it available here
Run SQLAzureMW.exe.
Select the Analyse/Migrate radio button from the right hand side of the main window.
Select the Target Server as “SQL Database latest service version (V12)”. Click [Next].
Connect to the SQL Server 2012 instance. Server name for my machine is “VAIOE\SQLSERVER2012”, use Windows authentication, select “Master DB (list all databases)” from the database options and “Save Login Information”. Click [Connect].
Select the required database to migrate [use GVH Report database for now]. Click [Next].
Select “Script all database objects”.
Click [Advance] and change the following options:
a. Under General set “Target Server” to “SQL Server”.
b. Under “Table/View Options” set “Script Table / Data” to “Table Schema with Data”. Set “Database Engine Stored Procedures” to “True”. Set “Security Functions”, “Security Stored Procedures” and “System Functions” to “True”.
Click [OK]. Click [Next].
Review your selections. Click [Next].
You will be prompted “Ready to Generate Script?”, click [Yes]. This will start the script generation. Once this is done, click [Next].
Now you will get another connection dialog. This time select the database on the target server (the SQL Server 2008 R2 instance). Select Master database so you get a choice of target DB. Click [Connect].
Now, it is likely that you want to migrate into a new database, so click [Create Database].
Enter a database target name and leave the “Collation” as the “”, this does not concern us. Click [Create Database]. Click [Next].
You will now be prompted “Execute script against destination server?”, click [Yes].
This will now go off and do loads of stuff, setting up the schema using the generated script, but unlike the previous method we found, the data is bulk loaded using BCP, which is blazingly fast. All of this is also done internally, so no generation of massive .sql script files etc.
Click [Exit].
You are done. Now if you open up Management Studio and connect to both the SQL Server 2012 and 2008 R2 instances we have just worked with you can see that the schema for the 2012 source database matches the target database which was just created.
The two processes above are almost identical and provide the same functionality. I would not perform the latter unless you specifically need to migrate to Azure or method 1 fails for you.
I hope this helps someone out.