Copy SQL Server 2005 Express database to SQL Server 2000

Angus (nospam@.gmail.com) writes:
> 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.
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
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
"Erland Sommarskog" wrote:

> 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
>
|||Shiggity (Shiggity@.discussions.microsoft.com) writes:
> 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
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