Copy Database Wizard Help

Copy Database Wizard Help

The Copy Database Wizard provides a convenient online way to move or copy databases and their objects from one server to another, with no server downtime. Using this wizard, you can:

  • Pick a source and destination server.

  • Select databases to move or copy.

  • Specify the file location for the databases.

  • Create logins on the destination server.

  • Copy supporting objects, jobs, user-defined stored procedures, and error messages.

  • Schedule when to move or copy the databases.

Important For the Copy Database Wizard to work, you must be a member of the sysadmin fixed server role on the source server, as well as on the destination server. In other words, you must be an administrator on the server from which the database is to be copied, as well as an administrator on the server to which the database is being copied.

Copy Database Wizard DTS Custom Tasks

The Copy Database Wizard is built as a Data Transformation Services (DTS) Package that runs on the destination server. After the wizard screens have been completed, Microsoft® SQL Server™ 2000 automatically names and saves the package on the destination server. The package is saved whether or not it is run immediately, scheduled for a later date, or scheduled on a recurring basis.

The DTS custom tasks are also available as stand-alone items in the DTS Designer, and can be used in any DTS Package (not just with the Copy Database Wizard). The five custom tasks are:

  • Database Move/Copy Task

  • Logins Copy Task

  • Jobs Copy Task

  • Master Stored Procedures Copy Task

  • Error Messages Copy Task

See Also

Ugrading Databases from SQL Server 7.0 (Copy Database Wizard)

DTS Package Elements

Using the Copy Database Wizard


Select a Source Server

Use this screen to specify the server on which the database to move or copy is located, and to enter login information. After you select the authentication method and enter login information (as required), click Next to establish the connection to the source server. This connection remains open for the duration of the copy or move session.

Options:

Source server

Select the name of the server on which the database or databases you want to move or copy are located, or click the browse (...) button to locate the server you want.

Use Microsoft® Windows® Authentication

Use your Windows user name and password to validate the connection.

Use SQL Server™ Authentication

Use SQL Server security to validate the connections.

User name
Enter a valid SQL Server standard login, if SQL Server Authentication is selected.
Password
Enter the standard login password, if SQL Server Authentication is selected.

Next

Start the validation process. This process checks whether the user is a member of the sysadmin fixed server role on the selected computer.

See Also

Authentication Modes

Ugrading Databases from SQL Server 7.0 (Copy Database Wizard)


Select a Destination Server

Use this screen to specify the server to which the database will be moved or copied. After you select the authentication method and enter user information, click Next to establish the connection to the destination server and to validate servers and user information.

To establish a connection:

  • You must be a member of the sysadmin fixed server role on the selected computer.

  • The destination server must be different from the source server.

  • The two servers must conform to version rules.
Options:

Destination server

Select the name of the server to which the database or databases will be moved or copied, or click the browse (...) button to locate a destination server.

Note You can use a destination that is a clustered server; the Copy Database Wizard will make sure you select only shared drives on a clustered destination server.

Use Microsoft Windows Authentication

Use your Windows user name and password to validate the connections.

Use SQL Server Authentication

Use SQL Server security to validate the connections.

User name
Enter a valid SQL Server standard login, if SQL Server Authentication is selected.
Password
Enter the standard login password, if SQL Server Authentication is selected.

Next

Start the validation process, and then move to the next screen.

See Also

Authentication Modes


Select the Databases to Move or Copy

Use this screen to select the database or databases you want to move or copy from the source server to the destination server. The following databases cannot be moved or copied:

  • Databases that exist on the destination server.

  • System databases.

  • Databases marked for replication.

  • Databases marked Inaccessible, Loading, Offline, Recovering, Suspect, or in Emergency Mode.
Full-Text Catalogs

When you move or copy databases that contain full-text catalogs, full-text functionality is stopped, and then must be manually restored. The Microsoft Search service manages full-text catalogs and runs in the database to provide this service. Full-text search is stopped by the Copy Database Wizard so databases can be placed into single-user mode, a requirement for a move or copy operation.

If you want to copy a database with full-text catalogs, full-text catalogs for the database are unavailable at the source server after the copy operation has been completed. You must manually repopulate full-text catalogs on the destination server.

If you want to move a database with full-text catalogs, none of the associated full-text catalog files are moved when the database is moved. These files must be moved manually by the database administrator.

Options

Move

Select to move the database to the destination server.

Copy

Select to copy the database to the destination server.

Source

Displays the databases that exist on the source server.

Destination

Displays the status of the source database on the destination server:

OK
The source database can be moved or copied.
Already exists
The source database already exists and cannot be copied.
System database
The source database is a system database and cannot be copied, although selected objects from the master and msdb databases can be copied.
Replication database
The souce database is involved in replication, and cannot be copied.
Other
For special cases, the specific status of the souce database will be noted.

Refresh

Refresh the list of databases.

Data & Log file location (Windows 98 servers only)

The list appears when a Microsoft® Windows 98 server has been selected as the source server. Select a location from the list to enable the Next button.

Click Next to verify that all data and log files for the selected databases are available at the selected location.

Next

Start the validation process, and then move to the next screen.

See Also

Upgrading Databases from SQL Server 7.0 (Copy Database Wizard)

Using the Copy Database Wizard


Database File Location

Use this screen to get an overview of the status for all the data and log files to be copied or moved. Files that are ready to be moved or copied have a checkmark in the Status field. If a name conflict or disk space problem exists, a red X appears in the Status field.

If there are disk space problems or naming conflicts, click either Modify or Next to open the Database Files Dialog Box, where you can resolve name conflicts and space problems.

Files

Displays the data files and log files that are included.

Destination drives

Displays the drive name of the destination server for the data files and log files. This information is set automatically using data from the registry of the destination server.

Size

Displays the size of the data or log file.

Status

Displays a green checkmark if the file is ready to be moved or copied.

Displays a red X if disk space problems or naming conflicts exist.

Modify

Click to open the Database File dialog box. Use this dialog box to resolve naming conflicts and/or to specify alternative drives if disk space problems exist.

Refresh

Clicked to reevaluate disk space and naming conflicts. Updates appear in the Destination drives and Status fields.

Note In Microsoft® SQL Server™ 2000, the Size field is not updated, even if the size of the files has changed. For example, updates could have been made to the list of databases selected.


Database Files Dialog Box

Use this dialog box to resolve conflicts encountered in a move or copy operation.

  • To resolve a name conflict, type a new name directly into the Source Files field.

  • To resolve disk space problems, click the browse (...) button to change the location in the Destination field.

After the name or disk space problems are resolved, click OK to proceed with the wizard.

Note You cannot free up disk space on destination disks using this dialog box.

Database

Displays the database name.

Destination Files

Displays the names of the files as they appear on the source server. To resolve a name conflict, you can edit this field directly. Select a file name, and then type a name for the file on the destination server.

Size

Displays the size of source files.

Destination

Displays the proposed destination location. To change the destination, you can edit the field directly or click Browse.

(...) Browse

Click to change the location of the database on the destination server. In the Standard File Location dialog box, locate the folder location you want, and then enter the database file name. You can use the same file name or change the database name.

Status

Displays a green checkmark if the file is ready to be moved or copied.

Displays a red X if disk space problems or naming conflicts exist.

Drive

Displays the drive name.

Available Space

Displays the available free space on each disk.

Required Space

Displays the disk space required for the proposed move or copy operation.

Refresh

Click to re-evaluate disk space and naming conflicts. Updates appear in the relevant fields.

See Also

Database File Location


Select Related Objects

Use this screen to modify which objects are to be included in the move or copy operation.

Options

Logins (recommended)

Include logins in the move or copy operation; selected by default.

All logins detected at package runtime
Select to copy all logins from the source serve. The list of logins copied is determined at run time.
Only logins used by the selected database (optional)
Select to copy only the logins used by the selected databases.

Shared stored procedures from the master database (optional)

Include stored procedures from the master database in the move or copy operation; selected by default.

Note Extended stored procedures and their associated DLLs are not eligible for automated copy in Microsoft® SQL Server™ 2000.

All stored procedures detected at package runtime
Select to copy all dbo (database owner) stored procedures in the master database (except those with name conflicts).
User-selected stored procedures
Select to enable the Select Stored Procedures from master Database screen, which provides a detailed selection of stored procedures in the master database.

Jobs from msdb (optional)

Include jobs from the msdb database in the move or copy operation; selected by default.

All jobs detected at package runtime
Select to copy all jobs in the msdb database (except those with name conflicts or associated with Replication).
User-selected jobs
Select to enable the Select Object from msdb screen, which provides a detailed selection of jobs in the msdb database.

User-defined error messages (optional)

Include user-defined error messages in the move or copy operation; selected by default.

All error messages detected at package runtime
Select to move or copy all messages (except those with ID conflicts).
User-selected error messages (optional)
Select to enable the Select User-Defined Error Messages screen, which provides a detailed selection of error messages.


Select Stored Procedures from the master Database (optional)

Use this screen to specify the stored procedures you want to include in the move or copy operation. This screen appears after you select User-selected stored procedures on the Select Related Objects screen.

Options

Source objects

Displays all the user-created stored procedures in the master database. Stored procedures that exist on the destination server cannot be copied; these are shown but not enabled on the list.

Owner name

Displays the owner of the object, for example, the special user dbo (database owner).

Type

Displays the type of the database object. Stored procedures are the only type used.

Destination

Displays the status of the source object on the destination server.

OK
The object can be moved or copied.
Already exists
The source objects already exist and cannot be copied.

Select All

Click to select all the objects listed in the Source field.

Clear All

Click to clear all the selected objects in the Source field.

Stored Procedures Copy Option

All stored procedures detected at package runtime

Use this option to copy all stored procedures detected at package runtime.

Selected stored procedures

Use this option to copy all the stored procedures.


Select Jobs from msdb (optional)

Use this screen to specify the jobs you want to include in the move or copy operation. This screen appears after you select User-selected jobs on the Select Related Objects screen.

Options

Source

Displays the source jobs available from the msdb database.

Type

In Microsoft® SQL Server™ 2000, jobs are the only object type that can be copied from the msdb database.

Destination

Displays the status of the source job on the destination server.

Select All

Click to select all the listed source jobs.

Clear All

Click to clear all the selected source jobs.

Jobs Copy Option

All jobs detected at package runtime

Use the option to copy all jobs detected at package runtime.

Selected jobs

Use this option to copy all the selected jobs.


Select User-Defined Error Messages

Use this screen to specify the messages you want to include in the move or copy operation. This screen appears after you select User-selected messages on the Select Related Objects screen.

All user-defined error messages appear on the list. You cannot copy a message if:

  • The message already exists on the destination server.

  • Conflicts exist with message ID numbers.

  • Error message IDs and language IDs conflict. For example, if (MessageId1, msglangid1) = (MessageId2, msglangid2), there is a conflict.

  • The language in which the message is written is not installed on the destination server.
Options

Error

Displays the error message number.

Severity

Displays the severity level of the message.

Message

Displays the text of the message.

Language ID

Displays the language identifier for the text of the message.

Status

Displays the status of the error message on the destination server.

OK
The object can be moved or copied.
Already exists
The source objects already exist and cannot be copied.

Select All

Click to select all the listed messages.

Clear All

Click to clear all the selected messages.

Error Messages Copy Option

All error messages detected at package runtime

Use this option to copy all messages detected at package runtime.

Selected error messages

Use this option to copy all the selected messages.

See Also

Error Message Formats


Schedule the DTS Package

Use this screen to specify when you want the move or copy operation to start.

Options

Package properties

Displays the name of the DTS package.

Name
Displays the name of the DTS package you plan to run. This name is automatically generated.

When

Specify when you want to run the DTS package

Run immediately
Select if you want the move or copy operation to start after you click Next.
Run once
Select if you want to run the move or copy operation only once, and then use On Date and On Time edit boxes to specify the time.
Schedule DTS package to run later
Select if you want to schedule the move or copy operation to start later. The current schedule settings appear in the description box. To change the schedule, click the browse (...) button to open the Edit Recurring Job Schedule dialog box.


Completing the Copy Database Wizard

This screen provides an overview of the selections you have made, including:

  • Databases selected and whether these will be moved or copied.

  • File locations for each database.

  • Logins to be included.

  • Any selected objects from the master database.

  • Any selected jobs from the msdb database.

Important When using the Copy Database Wizard to upgrade databases from Microsoft® SQL Server™ version 7.0 to SQL Server 2000, the administrator should run sp_updatestats (update statistics) against the database on the destination server. This is important for optimum performance of the copied database.

Back

Click Back to review or change your selections.

Finish

Click Finish to run the move or copy operation and view its progress in the Log Detail screen.

See Also

sp_updatestats


Log Detail Screen

The Log Detail screen appears if the move or copy operation is run immediately. This status screen shows the ongoing progress of each major step in the operation, as well as the start time, end time, and total elapsed time for each step. The screen can be expanded to view the detail of each step.

Step Detail

Status

Displays a green checkmark to indicate the step has completed successfully.

Displays a red X to indicate the step has failed.

Step Name

Displays the name given by the Copy Database Wizard to steps in the process. For example, CDW Master Task Step would refer to the transfer of object information from the master database.

Run status

Displays a numeric code to indicate the status of each step in the operation.

1
The step is waiting to be processed.
2
The step is currently running (executing).
3
The step is not currently active (execution inactive).
4
The step has finished running (execution completed).

Start Time

Displays the time when the move or copy operation starts.

End Time

Displays the time when the move or copy operation ends.

Elapsed time

Displays the total time taken for the operation.

Error Code

Displays the error message number if an error is raised.

Error Description

Displays the description of the error message encountered.

More Info

Expands the dialog box to show the Task detail of each major step.

Less Info

Collapses the dialog box to hide the Task detail of each major step.

View Error

Displays detailed description of the information in the Error Description field.

Task Detail

Status

Displays a green checkmark to indicate the step has completed successfully.

Displays a red X to indicate the step has failed.

Description

Displays a detailed description of the task.

Error Code

Displays an error message number if an error is raised. Ok indicates that no errors have been encountered.