Copy a DB From MSSQL 7 -> MSSQL 2k

[jassh]

i was given a task to copy a database and all its view
from one MS SQL Server 7 to another MS SQL SERVER 2000 Enterprise Edition.

How can i do this so that i get exactly the same database structure and data in the destination server?

i have used the copy database wizard and wierd error msg keep popping up. Perhaps because of version compatibality?

is there any way i can do this from the Enterprise Manager?
or is there any better alternatives?

thanks in advanced.
Happy New Year to everyone :)

-jassh

[ITGURU]

You can achieve this functionality either by using DTS (Data Transfer Service) or using system store procedure like sp_addlinkedserver.

For detail refer to SQL Server Books Online.

[jassh]

hi there ITGURU..

okay... i read about SP_ADDLINKEDSERER,
and i found out that, and i quote,

which allows access to distributed, heterogeneous queries against OLE DB data sources


i think this just link the server together so that the server
can then execute distributed queries.

But how do you copy all the tables, view, PK and what not
to the destination server. i tried using the wizard, but still no luck.

any SP i can use to copy or clone a certain database to another server?

thanks :)