Transferring data to new server

[itsmarkdavies]

I need some advice on copying databases, stored procedures, views, logins ..etc from a SQL Serevr 7.0 server, to a new SQL Server 2000 server.
Is it better to backup the databases on one server and then restore them to the other. Or id it easier to use the Wizards to Import everything from new server, or to Export everything from the old server ?

Any advice would be appreciated. I need to ensure that I do not miss anything, in particular Stored Procedures in the old master database. itsmarkdavies@hotmail.com

 

[DBCustard]

Consider detaching the database, copying the device files and reattatching them on the 2000 server. This is certainly the simplest method. ...

sp_detach_db mydatabase

(copy files to new server)

Do this on both servers ...
sp_attach_db mydatabase, 'datafilename', 'logfilename'

 

[tlbroadbent]

You can't detach the master database. You should not attempt this when upgrading. You should script logins and user objects in the system databases (master, msdb) and add them on the SQL 2000 server. Or use DTS to transfer the objects you want to retain.

The detach and attach procedure is a great way to move databases. However, the database users will probably not match logins on the new server. You'll need to synchronize users with logins. See thread183-212289 for more information about scripting logins and synching users and logins. Terry L. Broadbent - Salt Lake City, UT
Home of the 2002 Winter Olympics (Feb 8-24)

http://www.saltlake2002.com