Moving databases from one server to other(both are ver 7.0)

[sv]

Iam planning to move all the databases from one server to another, both servers are sql 7.0, can any one tell me how to transfer system databases using sp_detach_db and sp_attach_db procedure. I am comfortable in moving the user databases. How to move the logins and users across the servers. Help in this regard is highly appreciated.

[HemantH]

Use sp_detach_db [@dbname =] 'dbname' to detach each database from the server.
Copy the data files on to the new server location.
Use sp_fixlogins system stored procedure (undocumented) to create (copy) logins on the new server.
Use sp_attach_db [@dbname =] 'dbname', @filename1 = 'filename_n' [,...16] on the new server to attach the database files to each database on the new server.

- let me know if you have any questions...



------------
sv at 8/29/00 4:40:33 PM


Iam planning to move all the databases from one server to another, both servers are sql 7.0, can any one tell me how to transfer system databases using sp_detach_db and sp_attach_db procedure. I am comfortable in moving the user databases. How to move the logins and users across the servers. Help in this regard is highly appreciated.

[Gary Andrews]

I believe if you use DTS (the designer) creating a package to move
the database(s), you can have it also move the logins.

In EM, under console (left pane), click box for Data Transformation Services,
right click LOCAL PACKAGES, click "new package", this brings up the designer.

On the menu bar, click TASK.

On the sub-menu bar, click #5 (transfer SQL server objects).

This starts up a wizard. You can transfer objects and data.


------------
sv at 8/29/00 4:40:33 PM


Iam planning to move all the databases from one server to another, both servers are sql 7.0, can any one tell me how to transfer system databases using sp_detach_db and sp_attach_db procedure. I am comfortable in moving the user databases. How to move the logins and users across the servers. Help in this regard is highly appreciated.