2

We have a Microsoft SQL Server 2005 database that needs to be converted back to SQL Server 2000. Does anybody have any suggestions on the approach or tools I should use to perform this conversion? We don't utilise any SQL Server 2005 specific features in the database so this should not make the conversion problematic.

I should mention that I have found the Microsoft SQL Server Export facility very buggy in dealing with Auto Generated Keys so suggestions for alternative tools would be appreciated.

Improve this question
3
  • That sounds extreme. You probably have a reason for wanting to do so, but I would suggest reconsidering as 2000 is now an 8 year old technology ... the only way is up! Commented Oct 8, 2008 at 4:42
  • Doesn't switching from 2000 to 2005 cost money? :) Commented Oct 8, 2008 at 4:46
  • Was the database "upgraded", or was it created on 2005 in the first place? If it was "upgraded", it may be in compatibility mode for 8.0 anyway. What is the motivation for the downgrade back to 2000? Commented Oct 9, 2008 at 14:00

4 Answers 4

Reset to default
5

Generate a full script for your database in SQL2005, and change the "Script for Server Version" option to SQL Server 2000. You can now recreate your database on the SQL 2000 server. After this is complete, use the export data feature to export from SQL 2005 to SQL 2000.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

Get a trial of RedGate SQL ToolBelt, then use:

  1. SQL Compare to transfer the database
  2. SQL Data Compare to transfer the data
Improve this answer

1 Comment

I second this. RedGate does a good job of scripting out the correct sql statements when moving between db versions. The MS products tend to be generate script only readable by the same version.
0
  1. Script out the entire database
  2. Create a new SQL2000 database
  3. Run the script on the new database
  4. Import the data from the SQL2005 to the SQL2000 database (lots of ways to do this)

There probably is a tool somewhere that does all this for you, although I don't know how commonly this is done.

Improve this answer

Comments

0

What you want to do is called downgrading. Google gives lots of results, but here's a good walkthrough on a thread of Microsoft's forums (scroll down near the bottom of the page).

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.