Can I restore a MS SQL 2005 DB to a MS SQL 2000 DB using built-in restore (downgrade)?

Posted on 2007-08-12
Medium Priority
Last Modified: 2012-08-13
I just moved over my DB from MS SQL 200 to MS SQL 2005 and now I'm getting the odd error;
Brief Description: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

I was wondering if it's possible to backup the 2005 DB and restore back on my 2000 machine?
Question by:juliandormon
  • 2

Expert Comment

ID: 19680776
If you set the database compatibility down to 80 (SQL 2000), you should be able to easily downgrade the database and restore it on the 2000 machine.  Provided you haven't used any 2005 specific features yet.
LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 800 total points
ID: 19681915
I think I have to disagree, but I might be wrong (I actually never tried that out).
once you restored/attached a database on sql 2005, you cannot go back AFAIK
LVL 29

Assisted Solution

Nightman earned 800 total points
ID: 19682296
angelIII is correct - you cannot restore a 2005 database on a SQL 2000 server. The internal structures of the database will have been changed as part of the upgrade, and this is not compatibile with SQL 2000.

Assisted Solution

davecorun earned 400 total points
ID: 19683955
You could export the data to a common format, and then re-import into the SQL 2000 database if setting the compatibility doesn't work.

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question