The Copy Database Wizard lets you move or copy databases and their objects easily from one server to another, with no server downtime. You can also upgrade databases from a previous SQL Server version to SQL Server 2014. By using this wizard, you can do the following:
Pick a source and destination server.
Select databases to move, copy or upgrade.
Specify the file location for the databases.
Create logins on the destination server.
Copy additional supporting objects, jobs, user-defined stored procedures, and error messages.
Schedule when to move or copy the databases.
In addition to copying databases, you can copy associated metadata, for example, logins and objects from the master database that are required by a copied database.
In This Topic
Before you begin:
Limitations and Restrictions
Using the Copy Database Wizard to:
Copy, Move, or Upgrade Databases
Follow up, after upgrade:
After Upgrading a SQL Server Database
Limitations and Restrictions
The Copy Database Wizard is not available in the Express edition.
The Copy Database Wizard cannot be used to copy or move the following databases.
Databases marked for replication.
Databases marked Inaccessible, Loading, Offline, Recovering, Suspect, or in Emergency Mode.
After a database has been upgraded, it cannot be downgraded to a previous version.
If you select the Move option, the wizard deletes the source database automatically after moving the database. The Copy Database Wizard does not delete a source database if you select the Copy option.
If you use the SQL Server Management Object method to move the full-text catalog, you must repopulate the index after the move.
The detach-and-attach method detaches the database, moves or copies the database .mdf, .ndf, .ldf files and reattaches the database in the new location. For the detach-and-attach method, to avoid data loss or inconsistency, active sessions cannot be attached to the database being moved or copied. If any active sessions exist, the Copy Database Wizard does not execute the move or copy operation. For the SQL Server Management Object method, active sessions are allowed because the database is never taken offline.
Ensure that SQL Server Agent is started on the destination server.
To ensure optimal performance of an upgraded database, run sp_updatestats (update statistics) against the upgraded database.
When you copy a database to another server instance, to provide a consistent experience to users and applications, you might have to re-create some or all of the metadata for the database, such as logins and jobs, on the other server instance. For more information, see Manage Metadata When Making a Database Available on Another Server Instance (SQL Server).
In SQL Server Management Studio, in Object Explorer, expand Databases, right-click a database, point to Tasks, and then click Copy Database.
From the Select a Source Server page, specify the server with the database to move or copy, and to enter login information. After you select the authentication method and enter login information, click Next to establish the connection to the source server. This connection remains open throughout the session.
From the Select a Destination Server page, specify the server where the database will be moved or copied. If you set the source and destination servers to the same server instance, you will make a copy of a database. In this case you must rename the database at a later point in the wizard. The source database name can be used for the copied or moved database only if name conflicts do not exist on the destination server. If name conflicts exist, you must resolve them manually on the destination server before you can use the source database name there.
From the Select a Transfer Method page, select the transfer method.
From the Select Database page, select the database or databases you want to move or copy from the source server to the destination server. See Limitations and Restrictions in the 'Before You Begin' section of this topic.
From the Configure Destination Database page, change the database name if appropriate and specify the location and names of the database files. This page appears once for each database being moved or copied.
From the Select Database Objects page, select the objects to include in the move or copy operation. This page is only available when the source and destination are different servers. To include an object, click the object name in the Available related objects box, and then click the >> button to move the object to the Selected related objects box. To exclude an object, click the object name in the Selected related objects box, and then click the << button to move the object to the Available related objects box. By default all objects of each selected type are transferred. To choose individual objects of any type, click the ellipsis button next to any object type in the Selected related objects box. This opens a dialog box where you can select individual objects.
From the Location of Source Database Files page, specify a file system share that contains the database files on the source server. This is required if the source and destination server instances are on different computers.
The Copy Database Wizard creates a SSIS package to transfer the database From the Configure the Package page, customize the package if appropriate.
From the Schedule the Package page, specify when you want the move or copy operation to start. If you are not a system administrator, you must specify a SQL Server Agent Proxy account that has access to the Integration Services (SSIS) Package execution subsystem.
From the Complete the Wizard page, review the summary of the selected options. Click Back to change an option. Click Finish to create the database. During the transfer, the Performing operation page monitors status information about the execution of the Copy Database Wizard.
After you use the Copy Database Wizard to upgrade a database from an earlier version of SQL Server to SQL Server 2014, the database becomes available immediately and is automatically upgraded. If the database has full-text indexes, the upgrade process either imports, resets, or rebuilds them, depending on the setting of the Full-Text Upgrade Option server property. If the upgrade option is set to Import or Rebuild, the full-text indexes will be unavailable during the upgrade. Depending the amount of data being indexed, importing can take several hours, and rebuilding can take up to ten times longer. Note also that when the upgrade option is set to Import, if a full-text catalog is not available, the associated full-text indexes are rebuilt. For information about viewing or changing the setting of the Full-Text Upgrade Option property, see Manage and Monitor Full-Text Search for a Server Instance.
If the compatibility level of a user database was 100 or higher before upgrade, it remains the same after upgrade. If the compatibility level was 90 in the upgraded database, the compatibility level is set to 100, which is the lowest supported compatibility level in SQL Server 2014. For more information, see ALTER DATABASE Compatibility Level (Transact-SQL).