Just in Chronicles

Life as a Voyage

How to Restore Data to MS-SQL Server 2008 from MS-SQL Server 2008 R2

Like other DBMS, MS-SQL Server doesn’t support backward compatibility, in regards to the database backup and restore function. So, it’s not possible to restore data to MS-SQL Server 2008 (SQL 2008) from the MS-SQL Server 2008 R2 (SQL 2008 R2) backup. Officially SQL 2008 has its version number as 10.0 and SQL 2008 R2 has 10.5. However, instead of using the backup and restore function, we can simply generate SQL script to handle the database schema and its data. This is how we make this possible.

Step #0. Open the MS SQL Server Management Studio (MS-SSMS).

Step #1. Select the database you’d like to create script and right mouse click, and you’ll see the menu below.

Step #2. Now, click the “Generate Scripts…”, and “Generate and Publish Scripts” wizard will be popped up. Click “Next”.

Step #3. Select appropriate option. Here, we’ll choose the first option, “Script entire database and all database objects”. Click “Next”

Step #4. Choose the output option. We can simply select to save the script to our local machine or screen. Click “Advanced”.

Step #5. This is the most important step. Select the red boxed options accordingly. For fresh restore, all objects must be dropped and re-created. Therefore,  choose “Script DROP and CREATE” option. Server version must be “SQL Server 2008” as we’ll run this script on SQL 2008. Finally, “Schema and data” must be chosen as we’ll restore all objects and their data. Then click “OK”.

Step #6. Once the “Advanced” option is set, you’ll see the Step #4 screen again. Click “Next” and you’ll see the summary screen. Click “Next” again.

Step #7. The wizard creates the script we need. Click “Finish” and you’ll see the generated script on your screen or in your specific location.

That’s it! It’s not the perfect or desirable way to restore data to SQL 2008 from SQL 2008 R2, but useful workaround. If you get some error while you are running the generated script on SQL 2008, you might need to modify running order in the script.

Advertisements

Written by Justin Yoo

01/03/2012 at 11:05

Posted in For Database

Tagged with , , , ,