Recreate a 2008 DB on a 2005 Instance

Hey everyone, just a quick note on one way to get data from a 2008 DB to a 2005 DB. (EDIT: This initial "Mini Post" turn out a bit longer than I expected...)

As most of you would know you cannot restore a backup from a newer version of SQL Server on an older instance, so this limits the options you have if you do end up having to this for whatever reason.

Your best option is to generate a script of the DB on the newer instance and execute the script on the older instance.

You can choose to either script the data as well, or you can export the data to either a text file or Excel document and import the data into the 2005 DB afterwards.

First connect to your 2008 instance and right click the database, choose Tasks -> Generate Scripts.

Fig. 1: Generate SQL Server Scripts Wizard.

Very IMPORTANT step. In the "Choose Script Options" dialog box, you have a lot of choices in how/what you want to script. Of note in this particular example is:

• "Script for Server Version". Here you can choose what version of SQL Server to script for. For my purposes since I'm going backwards, I chose 2005.
• "Script Data". Here you can choose if you wish to script the data found in the tables. I found this only seems to work if there is a small amount of data in the tables (how has it worked out for anyone else? Comment below). I left this property as False.

Fig. 2: Choosing to script for SQL Server 2005.

Click Next.

Verify that the correct database is highlighted in the list. Leave "Script All Objects" unchecked so you can choose which object(s) you wish to script. Click next.

Under "Choose Object Types", I left 'Users' unchecked because I wanted to recreate the users later. Click Next.

Fig. 3: Choosing Object Types...

Fig. 4: ...Choosing the actual Objects.

After you have selected all your objects, choose to save script to a file then finish the scripting process.

Fig. 5: Successful scripting.

You have successfully scripted your 2008 DB. Now connect to your 2005 SQL Server instance and create a blank DB.

Open your saved script in Management Studio and run it on your blank DB (at the top of the script should be "USE [YourDatabaseName]"; change that to whatever you named the blank DB).

Execute the script. You have now created your 2005 version of your 2008 DB. If you chose to script the data as well, then the DB would be populated. If you did not, then you'll have to export the data (via the Import/Export wizard) from the 2008 DB and then import it into the 2005 DB.


DISCLAIMER: As stated, I’m not an expert so please, PLEASE feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)