Copy data from SQL2k to 2005Express DB

[Oleg Ogurok]

Hi there,

I need to copy a database from a SQL2000 server into a machine with Visual
Studio 2005 / SQL 2005 Express.
I've managed to duplicate the table structure using a script generated with
Red Gate SQL tools. What's the best way to copy the data over?

Both servers are on separate networks with no direct connectivity between
them.

I tried creating a script using Red Gate SQL Data Compare tool, but the
script came out to be 75mb and when I passed it to "osql" command, 5 minutes
into the process I got "Not enough memory to run this query" error.

-Oleg.

 

[David Browne]

Hi there,

I need to copy a database from a SQL2000 server into a machine with Visual
Studio 2005 / SQL 2005 Express.
I've managed to duplicate the table structure using a script generated
with Red Gate SQL tools. What's the best way to copy the data over?

Both servers are on separate networks with no direct connectivity between
them.

I tried creating a script using Red Gate SQL Data Compare tool, but the
script came out to be 75mb and when I passed it to "osql" command, 5
minutes into the process I got "Not enough memory to run this query"
error.

Backup the SQL 2000 database and restore onto SQL 2005, or detach the mdf
file and attach it. In either case SQL 2005 will upgrade the internal
structures of the database so this is a one-way operation.


David