Convert a MS SQL 2008 Database to SQL 2005

Scenario: We developed a database in MS SQL 2008 but wanted to house it on MS SQL 2005 server.

To convert it to SQL2005 use the following steps:

1)

On the SQL Server 2008:

Find the DB you want to convert

Right click the DB (GreenDB in our case) -> Tasks -> Generate Scripts. This will bring up a sub-menu. Click Next on the first screen then select the database objects you want to export (in our case we selected : Script entire database and all database objects)

Then click Next

The following screen you can choose how you would like the script to be saved. We chose to export it to file. When you’ve decided on that click Advanced.

 

 

You should now see the Advanced Scripting Options.

Ensure:
Script of Server Version = SQL Server 2005
Types of data to script = Schema and data

Click OK then Next

You will then see a summary. Click Next to complete

2)

When the script is generated copy the file over to your SQL server 2005. Open the sql file up as a new query in Studio Management and execute the script. (Alternatively just copy and paste the contents into a new query – if you didn’t save it as an .sql file)

As we selected to export the Schema and data this will create all the tables and import the data.

Note: You may need to change the location of the MDF and LDF files from within the file. Alternatively just create the database in Studio Management first then take out the CREATE DATABASE statement.

 

Job done.