Transferring Databases to another server

[jakhan]

hi

I have sql server 2000 with 20 databases on it.
i want to transfer all of those databases along with usernames to another (fresh) sql server.

please tell me the safest way to accomplish this task

thanks

Jahangir
IT pro.

 

[TheBugSlayer]

To transfer the databases:
1 - detach them one by one from the source server using sp_detach_db SP
2 - copy the files to a location on the destination server
3 - attach the files on the destination server using sp_attach_db, or sp_attach_single_file_db if you don't want to attach the log files and let SQL Server create new ones for you.

Note: in SQL 2K you can use EM to detach/attach the DBs, it's much easier.

To transfer logins:
In EM go to Data Transformation Services, create a new DTS package, use the Export Login task, save and execute the package.

NB: there are other ways of achieving the same results, but these are easier for you.

Hope this helps,
TheBugSlayer.