No 3rd party tools necessary. SQL Server 2008 Management Studio gave us a very powerful tool to down-convert a database because they added a "Script data" option to the "Script database" wizard.

Just right-click the DB in SQL2008 SSMS, and go to Tasks, then "Generate Scripts"

Walk through the wizard, make sure to chose "True" for "Script Data" under the table/view options. Choose all objects, then run the script it creates on the 2005 server. (Please keep in mind that the script created could bemassive if the original database is very large!)

Note that you can even run the wizard against a SQL2005 server to down-convert a SQL2005 database to SQL2000 (you would need the 2008 tools installed on your workstation, of course).

• 4
  No offense, but this can't be a solution if the database is big. Try that technique on any table with few millions rows (and think of varchar(max) as the data type of a single column) and if Management Studio will be able to open and parse this file you'll be happy, but I'm sure it won't open, it will crash. Sorry, but that's not the solution to this problem, except if the db is really small.

  Marian

  – Marian

  2011-02-04 16:36:57 +00:00

  CommentedFeb 4, 2011 at 16:36
• 3
  Sure, if the db is too large, script out just the database structure, then use your favorite method (SSIS, BCP, Import Wizard) to push the data.

  BradC

  – BradC

  2011-02-07 18:13:50 +00:00

  CommentedFeb 7, 2011 at 18:13
• In SSMS 11.0 there appears to be no option to script data along with the structure.i.imgur.com/SGkG8oZ.png

  jcollum

  – jcollum

  2014-11-11 22:30:50 +00:00

  CommentedNov 11, 2014 at 22:30
• Ah OK it's now under "Types of data to script" just above Table/View Options

  jcollum

  – jcollum

  2014-11-11 22:39:43 +00:00

  CommentedNov 11, 2014 at 22:39

You could BCP the data from one SQL Server instance to another instance. That would be the fastest way to copy the data from one version to another. Depending on the volume of data, it might take a long time.

• 2
  Depending on the volume of data it will almost always take a long time

  jcolebrand

  – jcolebrand♦

  2011-01-05 05:45:58 +00:00

  CommentedJan 5, 2011 at 5:45
• Yep, it sure will. Moving a large database to an older version of SQL Server isn't an easy task.

  mrdenny

  – mrdenny

  2011-04-15 07:32:10 +00:00

  CommentedApr 15, 2011 at 7:32
• 2
  The upside to BCP is that it's going to be faster than using script data. Yes, it's slow, but it's faster than many alternatives.

  anon

  – anon

  2011-04-16 22:27:20 +00:00

  CommentedApr 16, 2011 at 22:27

There is no direct way, that I know of, to downgrade a DB from 2008's format to 2005's unfortunately.

The way I have done this in the past (actually with older versions of SQL server, but the process will be the same) is:

1. Restore the DB on a SQL2008 instance if not done already
2. Build an empty DB with the correct structures (tables, indexes, constraints, views, procs, triggers, ...) on a SQL2005 instance. Hopefully you can do this from your existing build procedure and/or source code, but if not you can use SQL Server Manager to create scripts for everything in the 2005 DB and run the result on a blank one in the 2008 instance.
3. Ensure the two instances can see each other (i.e. there are no firewalls blocking connections if the instances are on different machines) and link them usingsp_addlinkedserver.
4. Copy all the data over from one DB to the other. If there are no foreign key constrains and similar issues in triggers to worry about you can just link the DBs together and cursor through the list of tables (selecting them from sys.objects) and running
   INSERT destinationserver.destinationdb.schema.table SELECT * FROM sourcedb.schema.table
   (orINSERT schema.table SELECT * FROM sourceserver.sourcedb.schema.table if you have linked the instances that way around)
   for each table. If you do have inter-table consistency enforcing constraints and triggers you will of course need to a be a bit more clever about the ordering of these operations, especially if you have cyclic constraints like a table with a constraint based upon itself (one holding hierarchy data, as a possible example).

It may be more efficient to just copy the data first and add all the other structure (indexes, procs, triggers, ...) after step 3. This avoids row insert ordering issues caused by constraints and triggers, and building the indexes at the end should in theory be quicker than building them as all the data is added - though if you have any clustered indexes on your tables create these before adding the data as they would not be faster to create after the fact.

Of course this all assumes that none of your objects use SQL 2008 specific features - if they do you will hopefully find out and fix such things when things error upon rebuilding the schema. If any of your code relies on officially undefined behaviour that happens to have varied between SQL Server versions you may have some much more subtle and elusive bugs to hunt down and iron out later.

• 1
  -1 because this is actually inefficient (select * into from) compared to the likes of BCP.

  jcolebrand

  – jcolebrand♦

  2011-01-05 05:46:36 +00:00

  CommentedJan 5, 2011 at 5:46
• @jcolebrand fair enough on efficiency. Though the technique is one that I've had work.

  David Spillett

  – David Spillett

  2011-01-05 10:06:16 +00:00

  CommentedJan 5, 2011 at 10:06
• it's a fair system, I just thought I would include that on account of future readers here. You've got the upvotes currently for it not to be an issue. ;)

  jcolebrand

  – jcolebrand♦

  2011-01-05 15:15:41 +00:00

  CommentedJan 5, 2011 at 15:15

I faced similar situation and used Database Publishing Wizard to script schema and data for SQL Server 2005 from SQL Server 2008 database.

SQL Server Hosting Web Service (and toolkit)

Database Publishing Wizard

You first have to script the database and make sure on the version you specify the type you want to down grade to. And to copy the data from the higher version to the lower version SQL Data compare will do you the trick for you.

Good luck!

• sql-server-2005
• sql-server-2008