Restore Sql Express (2005) to Sql Server 2000

Well, my understanding is this should be possible and easy -- use a backup created by Sql Express (via Management Studio Express), copy the backup to the Sql 2000 server, select Database Restore through EM, specify Restore from Device, point to the Sql Express generated backup file as the *only* device, and launch.

Doesn't work.

When I launch the restore, I get the following error dialog:

Too many backup devices for backup or restore. Only 64 are allowed. <yada-yada> .... OK

Only got one backup device selected. Is this possible or not?

I have resorted to sql script generation with data in the past, but that's taxes the server resources heavily.

TIA,

Rick

No ... its not possible.. you can't restore SQL Server 2005 backup in SQL Server 2000. You may follow these steps

(a) Create Script of Database Objects from 2005

(c) Create new database in sql server 2000

(d) run the objects script in 2000

(e) use DTS to transfer data from 2005 to 2000

Madhu

|||Thanks. I assume you have to use DTS provided with full blown Sql Server 2005 (which ain't provided with SqlExpress)?
|||

Yes , SSIS(DTS of 2005) is not provided with Express edition. you can use sql server 2000 DTS and pull data from 2005 to 2000.

Madhu

|||Madju,

Okay ... so I've created the database objects in Sql Server 2000, created a System DSN to connect to the Sql Express database (different server).

Next, when I use 2000's DTS and specify SQL Native Client I am able to connect successfully to the Sql Express database. However, after only several seconds into the transer, I get the follow error from DTS:

Cannot perform requested task because full-text memory manager is not initialized

Not sure what that pertains to.

(later)
Okay, I've found it. It's the MSSearch service on the server that it needs running. I've started the service, stopped and restarted MS Sql Server serice, dependent services and EA says full-text support is running now. Guess what? DTS still yields the same error message! Any other idea, anyone? I'm running DTS from my workstation. I shouldn't have to run it from the server, should I?

(later)
For whatever reason, have to run DTS from the Sql Server 2000 server and not a client workstation. Done deal now.

|||Thanks. This was really helpful. I tried a similar approach by exporting the table defs and data from SQL Express 2005 using the data publishing wizard, but i ran into an error recreating the database on the SQL Server 2000 due to the limit of 64k for command lengths.

This method worked much smoother. I also had to run the DTS on the server itself for it to work.