SQL Server 2005: The Copy Database Wizard

Contributed by Rob Rogers  
Tagged: Database  

• Tweet

Browse to next tech-recipes article

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.

You may also like -

SQL Server 2008/2008 R2: Move tempdb to Improve PerformanceSQL Server 2005: How To Set Up Database MailCreating Databases the Quick, Easy Way With OpenOffice.orgSQL Server 2005: Move tempdbSQL Server 2005: How to Attach (or Reattach) a Database Missing the LDFSymfony: Fix propel-insert-sql error: Can't connect to local MySQL serverMicrosoft SQL Server: How to Find the Foreign Keys in a DatabaseCreate a MySQL database

 

12 Comments -

1. a said on December 8, 2008

   “4. Right-click the database that you want to copy.
   5. The Copy Database Wizard window will appear.”

   There is no such option

2. Evgen said on December 19, 2008

   yes.. i also have no wizard option… please help

3. domenic said on December 19, 2008

   4. Right-click the database that you want to copy
   5. chose the option “tasks”
   6. there in the new list click “copy database…”

   i think the rest is clear :)

4. rsc said on December 19, 2008

   you should search in Tasks for given database ;-) hope that helps

5. Evgen said on December 23, 2008

   under tasks i only have detach, shrink, back up, restore and generate scripts.
   can Create to… help me in any way? or backup database and then restoring it in a new name (if it’s possible)?

   thank you for your help.

6. xps said on February 18, 2009

   Are you using SQL Server 2005 Management Studio Express ? ’cause that’s what I use and their is no such option as copy database…

7. Nadia said on February 19, 2009

   in the performing operation , i got
   error in create package

   help.

8. dfsd said on February 26, 2010

   sdfsdfsd

9. Timok said on March 26, 2010

   In the “Configure the Package” screen, it tells me of the location to find the package created, such as ACHILLE_MSSQL2DTS PackagesCopy Database Wizard Packages.
   But so far I have been unable to locate the newly created package so I could run it again at will.
   Please help.

10. Ankush said on December 26, 2010

    i am not getting copy wizart otion in sqlsserver 2005

    ankush

11. Ankush_2083 said on December 26, 2010

    give me visual datail so i am getting

12. Talinkio17 said on December 27, 2010

    First, you need to have sys admin permissions on the source and destination. If you use the detach method you also need to have file permissions on both the source and destination.

    Second, you need SS 2005 sp2 for both to have all the features. It may work with less but options will be limited.

    This link from MS gives a good explanation:
    http://msdn.microsoft.com/en-us/library/ms188664(v=sql.90).aspx

RSS feed for comments on this post. TrackBack URL

Leave a comment -