The Copy Database Wizard

SQL Server includes a new tool to copy databases between two servers: the Copy Database Wizard. The Copy Database Wizard is implemented as a custom DTS package with the following custom tasks:

• Database Move/Copy Task—Using this task, you can select source and destination servers, as well as a database or databases to move or copy. Using this custom task, you specify the location for the database files in the destination server.

• Logins Copy Tasks—Used to copy existing logins from the source to the destination server.

• Master Stored Procedures Copy Task—Used to copy user-defined stored procedures from the master database in the source server to the master database in the destination server.

• Jobs Copy Task—Used to copy jobs from the msdb database, in the source server, to the msdb database, in the destination server.

• Error Messages Copy Task—Used to copy the messages contained in the master.dbo.sysmessages system table between the source and destination servers.

To illustrate how the Copy Database Wizard works, copy a database from one instance of SQL Server to another instance in the same server. If you have two servers in your network with SQL Server 2000 installed, you can follow this example to copy a database from one server to another.

Before starting with the wizard, you must create a new database. In this example, we created a new database called TimDB.

To start the Copy Database Wizard from Enterprise Manager, you can display the context menu for the Databases folder, as in Figure 14.23.

When you start the Copy Database Wizard, you get the Welcome to the Copy Database Wizard form, where you can find a summary of the actions that this wizard will execute.

Click Next, and you will see the Select a Source Server step (see Figure 14.24). In this form, you must select the SQL Server source server and specify which authentication mode the package will use to connect to the source server.

Click Next and the wizard will take you to the Select a Destination Server step (see Figure 14.25). Here you will select another instance of SQL Server 2000 in the same or a different server from the source server.

The next step is Select the Databases to Move or Copy (see Figure 14.26). In this form, you will see that only databases that do not exist in the destination server are available for moving or copying. This excludes system databases, because they exist in both servers. In this example, you must select to copy the database you just created at the beginning of this section.

Click Next and you will arrive at the Database File Location step (see Figure 14.27). In this form, you will see every file used by every database selected in the previous step.

The wizard selects as the destination the default data directory of the destination server, which is the directory where the master database primary data file is stored. You can change the directory of these files to any valid folder in the destination server.

Click Modify to go to the Database Files form, where you can change the name and location of every file individually (see Figure 14.28). If the file already exists on the destination directory, you will see a conflict mark. Note that you cannot change the file size in this wizard. Database files retain their size after the transfer to the destination server.

In SQL Server 2000, every database is as self-contained as possible. How ever, your database might need some objects that are defined in other databases. This is why the Copy Database Wizard contains the Select Related Objects step (see Figure 14.29). In this step, you can

• Select to copy logins from the source server to the destination. This is a recommended practice to avoid orphan database users in the destination database, because of nonexisting logins in the destination server. You can specify which logins to copy.

• If you created stored procedures in the master database to be shared by every database, you can transfer them, too. Perhaps the source database contains stored procedures and triggers that access the shared stored procedures from a master.

• You can have jobs defined in msdb that are referenced from your source database. You can copy them to the destination server, also.

• Perhaps you defined custom error messages in the source server and your stored procedures, triggers, and user-defined functions use these custom messages; then, you will need to transfer them to the destination server.

The next step is Schedule the DTS Package, because the Copy Database Wizard creates a DTS package and stores it in the destination server. Figure 14.30 shows this form, where you can provide a name to the DTS package and specify a schedule in the usual way.

The Completing the Copy Database Wizard form will show a summary of the tasks to execute, and you can click Finish to complete the wizard's work.

The package execution starts, as you see in Figure 14.31, and you can click More Info to get a full description of the tasks to execute (see Figure 14.32).

As you can see in Figure 14.33, the Copy Database Wizard executed the fol lowing tasks successfully:

What's Next?

Transferring and transforming data is a common administrative task in multiserver environments.

Chapter 15, "Working with Heterogeneous Environments: Setting Up Linked Servers," discusses the multiserver environment and the implications of distributed transactions. In Chapter 15, you learn how to use linked servers to maintain data in multiple servers, as an alternative to DTS and replication.