restoring SqlServer 2005 db back to SqlServer 2000 db

grahamor

Hi,

I have been working at home on a nixer using Sql Server 2005 express edition as the db. I now need to upload it onto the work server which runs SqlServer 2000.

Would it be possible for me to make a backup of the db and then just restore it onto the work server ??? (i cant try it until monday).

I dont imagine it is this easy so any advice is appreciated.

bonkey

I'm almost certain that the DB isn't backwards compatible.

If the two machines were networked, and you had access to the Data Transfirnation Services in SQL 2000 (I don't think you get the equivalent with 2005 ExpEd) it'd be fairily simple, but thats not much help if one's at home and the other at work.

Does bcp come with ExpEd? If so, you can create scripts for all the objects, and use bcp to dump and load the data to "flat" files.

jc

grahamor

managed to use the SQL Server 2005 script generator wizard to script the whole DB to a new query window in 2000 format, which has just saved me a couple of hours, cool !

bonkey

grahamor wrote:

managed to use the SQL Server 2005 script generator wizard to script the whole DB to a new query window in 2000 format, which has just saved me a couple of hours, cool !

Doesn't bring your data though, does it?

If not, Thats what you might want bcp for.

amen

I think you could also add it as a Linked database and export your schema and data that way

don't forget that the SQL security model is a bit different in 2000/2005

Kernel32

SQL Server 2005 has a compatibility level you can set per database. You can set a database to be compatible with SQL 2000 (80 for SQL 2k). You should then be able to backup and restore to SQL 2000.