Export & Import Data+Attach Database of Higher Version to Lower Version

TIP#1 FOR:

Uploading database of higher version of DBMS to the lower version one. In simple words, if you have got database file, that is .mdf or .bak file of the higher version DBMS or MS SQL Server 2012/2008 R2 (10.0.1650) and at the going moment, you have installed MS SQL Server 2008 or 2005 only. How will you upload/attach the required database to the lower version DBMS or MS SQL Server 2008 R2/2008/2005.

If you are the one who has messed up in the above stated scenario, then you should follow the steps given below to avoid the need to install MS SQL Server 2008 R2 (10.0.1650) immediately.

Step 1

Simply, ask for the ‘sql script’ file of higher version of database. That means, required person will export the database in to script at their end.

You may tell to follow these steps to script whole database as SQL scripts. (Right click on the required database name-> Right click select Script Database as-> Create to-> New file & save it as .sql.

Immediate steps will give you the commands to create database and all other database objects.)

To get database data-> Right click on the name of your desired database -> go to Tasks -> Export data option and follow the required wizard steps to export it as Excel or .xls format.

Including these steps, you may reference this one link to them.

Step 2

Once you would have received both files, make a backup of those files and save at some safe or easy to remember location.

Step 3

Now, switch back to MS SQL Server 2008 Query window. Use master database.

Step 4

Create the database by appropriate name.

Step 5

The open the duplicate copy of .sql file and select all commands after the database creation, that is leave create database to end block of it & copy (ctrl+c).

Step 6

Switch back to MS SQL Server 2008 Query window and paste it here. Caution change database by typing the following command ‘use yourdatabasename;’(without quotes) and execute it.

Step 7

You will see a confirmation in the output window.

(So, it is time to upload data to the required tables from the .xls file.)

Step 1

Right click over the database name and go to Task and import data.

Step 2

Data source- Microsoft Excel

Step 3

On the same window or step number 2 of the import data wizard, give the path of your database .xls file & select the Excel version if required. Click next.

Step 4

Then it will show optimized tables's name, select the desired table's name row to fetch data from.

Step 5

Check the option of SSIS package if required otherwise leave it blank or unchecked.

Step 6

*Finish* To confirm, whether you have uploaded data successfully or not, right click over database name & refresh it.

Then come back to query window run <select * from tablename;> (without angel brackets) command.

If it has been successfully imported, then it will show data in the output window.

Final tip of this exercise: If you find any problem or confusion in the above described steps; you should bring that problem here in the comment box. I will be happy to solve it.

Thank you!