SQL Server - Copy SQL Server 2005 Express database to SQL Server 2000

Asked By Angus

10-May-07 01:44 PM

Hello

I have a copy of a SQL Server 2005 Express database - the mdf and .ldf
files.  If I copy to my machine and in the Enterprise manager try to attach
the files I get error 602 - and apparently you cannot do this as the
database structure has changed so much.

Bearing in mind SQL Server 2005 is customersw so don't really want to
install too much software on their machine.  so if I can do this all my end
that would be preferable.  Can I somehow convert the 2005 database to run on
my SQL Server 2000?  What would I need to install on my machine to achieve
this?  The SQL Server 2005 Express does not seem to provide much in the way
of data export etc.

Angus

11-May-07 02:53 PM

I think the easiest solution is to go to Properties|Options for the database
and change the compatibility level to (80) Sql Server 2000 then backup the
database and restore it to the new 2000 server.

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

14-May-07 01:36 AM

Angus (nospam@gmail.com) writes:

There is a fair chance that the database uses features that are not
available in SQL 2000, so a transfer to SQL 2000 is non-trivial, at least
a priori.

If you think that SQL Express does not cut it for you, I would suggest
that you cough up the 50 USD or similar for a license of Developer Edition
of SQL 2005.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

14-May-07 01:36 AM

Dave Patrick (DSPatrick@nospam.gmail.com) writes:

No, this does not work. The compatitibility level only affects how T-SQL
code is parsed and behaves. It has nothing to do with the on-disk structure
for the database.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

13-May-07 05:12 PM

Oops, Ok thanks for clarification Erland.

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

01-Jun-07 02:00 PM

The solution is not to "cough up" money for SQL 2005.  What if someone else
reads this and needs to deploy to 2000 on a customer's server?

Do this:

In Management Studio, right-click on the Database, goto Tasks->Back Up
Back up to Disk,
Add path where file should go.

In Enterprise Manager, add a new database.
Highlight the database.
go to Tools -> Restore Database
Restore From device
Click Select Devices
Restore from disk, Add .bak file from Management Studio
Make sure paths at Options -> Restore As are correct

03-Jun-07 04:25 AM

Shiggity (Shiggity@) writes:


Not sure what you mean to say here, but if the intention is to describe
how to copy a database from SQL 2005 to SQL 2000, I'm afraid that you
method will not work. If you need to do that, you will have to script
the database (there is a SQL 2000 compatibility switch in the scripting
wizard) and bulk-copy data over. If database uses features that are not
supported on SQL 2000, you will have deal with that.

In the original post, Angus wanted to be able to work with a copy of
a customer database in SQL 2000. To that aim, he needs SQL 2005. And
if Express does not have what he needs, Developer Edition is his best bet.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx