The Copy Database Wizard is a very valuable asset when you are wanting to copy or move a database and its objects to another server. To utilize this functionality…
1. Open SQL Server 2005 Management Studio.
2. Connect to the server.
3. Expand the Databases folder.
4. Right-click the database that you want to copy.
5. The Copy Database Wizard window will appear.
6. Click Next.
7. Select the source server (the server containing the database) and proper authentication type.
8. Click Next.
9. Select a destination server and appropriate security.
10. Click Next.
11. Select the transfer method you wish to use for copying the database. The detach and attach method requires you to take the database offline while the SQL Management Object method allows you to keep the database online for making copies on the same server.
12. Click Next.
13. Check the Move or Copy checkbox next to the database that is to be moved or copied.
14. Click Next.
15. Use the default settings for the Configure Destination Database screen.
16. Click Next.
17. The Select Database Objects screen allows you to copy additional database objects including stored procedures and logins. Select the necessary objects and click the arrow button to move them to the Selected related objects column. If none are required, leave the defaults.
18. Click Next.
19. Use the defaults on the Configure the Package screen.
20. Click Next.
21. Select the radio button next to Run immediately.
22. Click Next.
23. The Complete the Wizard screen will appear and provides a summary of the selections you made.
24. Click Finish to initiate the copy or move.
25. A Log Detail screen will appear and list each part of the job as it is executed.
26. Click Close to complete the wizard.