How to restore a SQL Server 2012 database to SQL Server 2008 R2?

Question

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.

Solution 1

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:

1. In the 2012 instance, right click on the database you want to copy/"downgrade" and select "Tasks" > "Copy Database...".

2. "Welcome to the Copy Database Wizard" click [Next].

3. "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]

4. "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]

5. "Select the Transfer Method" For the sake of this example, select "Use the SQL Management Object method", click [Next].

6. Select to move or copy the required databases, click [Next].

7. Configure the destination database path and logical names etc. Select the required option for if the database exists. Click [Next].

8. Configure the integration services package, click [Next].

9. For this example, select the "Run Immediately" option for "Schedule the Package" options, click [Next].

10. "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

1. Run SQLAzureMW.exe.

2. Select the Analyse/Migrate radio button from the right hand side of the main window.

3. Select the Target Server as “SQL Database latest service version (V12)”. Click [Next].

4. 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].

5. Select the required database to migrate [use GVH Report database for now]. Click [Next].

6. Select “Script all database objects”.

7. 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].

8. Review your selections. Click [Next].

9. You will be prompted “Ready to Generate Script?”, click [Yes]. This will start the script generation. Once this is done, click [Next].

10. 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].

11. Now, it is likely that you want to migrate into a new database, so click [Create Database].

12. Enter a database target name and leave the “Collation” as the “”, this does not concern us. Click [Create Database]. Click [Next].

13. You will now be prompted “Execute script against destination server?”, click [Yes].

14. 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.

15. 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.

Solution 2

Right click on your database and select Tasks -> Generate Scripts

Select things you want to restore or just let the complete database be selected by default.

Now click Next -> Click Advanced and Choose 'Script for Server Version' to the 'ssms version' you want to restore like 2008 or 2008 R2 or other.

also, select 'type of data to script' to 'Schema and Data'

Finally execute this .sql file on destination ssms and Change Database Name in first Line Use Database

if database is not there then create one and if you get error with executing the script execute the script from alter keyword to complete bottom.

Solution 3

You can't, you can NEVER restore from a higher version to a lower version of SQL Server. Your only option is to script out the database and then transfer the data via SSIS, BCP, linked server or scripting out the data

Solution 4

To: Killercam Thanks for your solutions. I tried the first solution for an hour, but didn't work for me.

I used scripts generate method to move data from SQL Server 2012 to SQL Server 2008 R2 as steps bellow:

In the 2012 SQL Management Studio

1. Tasks -> Generate Scripts (in first wizard screen, click Next - may not show)
2. Choose Script entire database and all database objects -> Next
3. Click [Advanced] button 3.1 Change [Types of data to script] from "Schema only" to "Schema and data" 3.2 Change [Script for Server Version] "2012" to "2008"
4. Finish next wizard steps for creating script file
5. Use sqlcmd to import the exported script file to your SQL Server 2008 R2 5.1 Open windows command line 5.2 Type [sqlcmd -S -i Path to your file] (Ex: [sqlcmd -S localhost -i C:\mydatabase.sql])

It works for me.

Solution 5

You won't be able to restore from 2012 to 2008. You will be able to use a tool like red-gate SQL compare to copy the schema etc (provided nothing 2012 specific is used). If you have data to copy across too, you can use their Data Compare tool, and I think you get a 14 day free trial.

View more solutions

Comments

[Sandr]

In this case need to take in mind that new data types, possible used, need to be converted to the older ones - both in the scripts for schema creation and data migration...

[bolilloBorracho]

I didn't downvote you but it's probably because this solution does not work. It only looks like it works. If you take a closer look at your destination database, you will notice it did not copy the primary keys and indexes. Well... it copies the data but doesn't flag them properly for some reason.

[MoonKnight]

No, this copies key and everything correctly. I am not sure what you have done, but the above works just fine for me. The accepted answer saying this cannot be done is incorrect...

[bolilloBorracho]

OK thanks for standing your ground Killer. I tried the second method and it worked great. And it was incredibly fast. So thanks it did help. (I tried the first method again as well and still maintain that it does not copy the primary keys and indexes).

[Rumi]

Thanks a lot killercam. I have tried the second method on my SQL Server 2008 R2 installed computer. Without installing anything relating to SQL 2012, I have managed to connect to a SQL 2012 database and copied it to my server using SQLAzureMW 3.16.

[Don Jewett]

Method 1 requires SQL Server Agent running on the destination server, so didn't work for me in this case. However, Method 2 worked great for me. Thanks!

[Taylor Brown]

Method 1 failed for over an hour with me making tweaks, finally tried method 2 and worked in minutes. Thank you so much for this wonderful solution. If anyone is wondering, I went from 2012 to 2005.

[MoonKnight]

How was it failing? Sound like this answer itself, might be out-of-date!

[Michael Sobczak]

Just tried solution #2, and it worked fine. Brilliant!

[StackTrace]

Method 2 just saved 99 hours of hair pulling

[MoonKnight]

It seems that Method 1 might need re-addressing. It did work for me, but that was a while back now. Did you attempt method 1?

[Angel Naydenov]

Right now the URL for method 2 returns a 404 - Not Found. For some reason this project is no longer in CodePlex. I've searched to find to where it was moved if it is moved but all I found was getting me back to CodePlex. So not sure what happened there but it is no longer available :(

[Rumi]

Copy Database 2014 to 2008R2 failed: InnerException-->The database 'XY' cannot be opened because it is version 782. This server supports version 663 and earlier. A downgrade path is not supported. Could not open new database 'XY'. CREATE DATABASE is aborted.

[MoonKnight]

Is this a question?

[Mr.J]

@MoonKnight I'm getting an error "Dependencies for SQLAzureMW is missing" sorry for this question

[toscanelli]

Couldn't work with method 1, but mehot 2 worked successfully. Good job!