How to Use the Copy Database Wizard in SQL Server

In this article, we look at the features of the copy database wizard for moving objects from one SQL instance to another. We further look at restrictions and tips to get past them.

A SQL Server instance comprises of several objects, and if you ever you need to move those objects from the given instance to another then manually transferring them will not be a wise thing to do. For transferring your SQL objects from one instance to another you should make use of the Copy Database Wizard. Using this wizard you cannot only copy certain database objects but also complete databases, that too without any kind of server downtime. By making use of this wizard you can perform the following actions smoothly and in no time.

  • Select any source and the destination server.
  • Choose databases that you wish to shift or copy.
  • Mention the file locations for databases.
  •  Copy the desired logins on the destination server.
  • Copy stored procedures, supporting objects, jobs, as well as error messages.
  • Decide the data and time when you would want to shift or copy your databases.
How to Use the Copy Database Wizard in SQL Server

Apart from all that you can do when making use of the Copy Database Wizard, you also need to know what is all that you cannot do with it. Out of all its limitations and restrictions, the most crucial one you need to be aware of is that it will not be found in Express Edition of SQL Server. The other limitations of the wizard are given below

Limitations and Restrictions in Copy Database Wizard in SQL Server

  1. You cannot make use of the Copy Database Wizard for moving databases that are system, marked for replication, marked inaccessible, offline, loading, recovering, in Emergency mode, or suspect. It also restricts you from moving databases that have log files or data present in MS Azure Storage.
  2. This does not allow you to move or copy your database to an older SQL Server edition.
  3. If you have selected the Move option, the wizard will automatically delete the source of the database once it is moved. However, if instead of Move you select the option for Copy, the source will not be deleted. With Copy option the server objects that you select will be copied and not moved, only the database will move.
  4. If you make use of the detach and attach option, it will detach the database and reattach its multiple files to a new location after moving or copying them. There is a possibility of data loss and inconsistency in this method it is therefore advised to avoid attaching active sessions to the databases that are undergoing the moving or copying process. In-case of Management Object Method in SQL Server, users are allowed an active session as the database never goes offline.
  5. If you are transferring the Agent jobs in SQL Server, which are referring the non existing databases on the destination server, you will end up causing the entire operation to fail. A workaround for this problem will be to create Agent jobs in SQL Server before the database is created.

When you plan to keep the SQL Server database in optimum shape, do consider keeping a SQL Server repair tool nearby to deal with incidents of data errors.

Author Introduction:

Victor Simon is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including access recovery and sql recovery software products. For more information visit www.datanumen.com