Copy a SQL Server 2005 database into a SQL Server 2000 instance?

45 pts.
Tags:
Copy programs
Microsoft SQL Server 2000
SQL Server 2005
Hi, Greg. After an upgrade from SQL Server 2000 to SQL Server 2005, the application team would now like to go back to SQL Server 2005 without data loss. We have built a new SQL Server 2000 instance for them, but now need to get the user databases copied over or restored. What is the best way to accomplish this? Thanks for any help you can provide.

Answer Wiki

Thanks. We'll let you know when a new response is added.

Oops , typo in question. It should say the application team would now like to go back to SQL Server 2000 without data loss.

Moving from SQL 2005 to SQL 2000 isn’t quick or easy. There is no way to restore a SQL 2005 database to a SQL 2000 database. You will need a decent outage window to move the data.

You can try the copy database wizard. If your database isn’t that complex it may work for you.

If the database is to complex for the wizard you will need to script out all your objects then import the data using the import/export wizard or by manually creating a DTS/SSIS package.

I’m curious, why the move back from SQL 2005 to SQL 2000?

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Denny Cherry
    Check out my SQL Server blog "SQL Server with Mr Denny" for more SQL Server information.
    66,140 pointsBadges:
    report
  • Kmcfry
    Thanks, Denny. It's a large complex vendor application (1300 tables, many indexes, many constraints, many stored procedures, etc). Several performance problems exist. Some have been corrected by applying hotfixes and modifying configuration parameters, but now there is code that needs to be changed. The vendor cannot get this done in a reasonable timeframe, so now we have to roll back. Lessons learned, don't upgrade your QA environment until you are sure that you will be moving ahead into production. One of my collegues tried the SQL Server native export/import and that did not work because it does not remove the SQL Server 2005 embedded tags in the objects.
    45 pointsBadges:
    report
  • SqlServer 2000 & 2005
    Use Database publishing wizard a tool from Microsoft which is free. Using that you can revert back you database from sql2005 to sql 2000
    40 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following