6U SING THE C OPY D ATABASE W IZARDIN THIS EXERCISE, YOU WI...

EXERCISE 8.6

U ^{sing tHE} C ^oPy d ^ataBasE W ^izard

In this exercise, you will use create a database named CopyMe and load

it with some data. You will then use the Copy Database Wizard to copy

the database to the same SQL Server instance. Finally, you will verify that

the copy worked by looking at the data in the copied database as well

as viewing the SSIS package and SQL Server Agent job that the wizard

creates. This exercise assumes that you have administrative privileges on

the SQL Server you are working with.

1. In SQL Server Management Studio, open a new query window

that is connected to your instance of SQL Server.

2. Run the following statement to create the database we will copy:

CREATE DATABASE CopyMe;

3. Run the following statement to populate the database with some

data. This way, you can make sure the copy worked by ensuring

the same data is in the copied database:

SELECT *INTO CopyMe.dbo.Product FROM AdventureWorks2008.Production.Product;

4. Connect to the SQL Server instance in the SSMS Object Explorer.

5. Expand the Databases node and right click on the CopyMe

database.

6. From the pop-up menu select Tasks | Copy Database…

7. On the Copy Database Wizard first page click “Next.”

8. On the Select a Source Server page, in the Source Server field

enter the name of your SQL Server Instance and select Windows

Authentication. Click Next.

9. On the Select a Destination Server page, in the Destination

Server field, leave the value at (local), and again select Windows

10. On the Select the Transfer Method page, select Use the detach

and attach method. Leave the If a failure occurs, reattach the

source database checkbox checked. Note: you can ignore the

warning about the SQL Server Agent needing a proxy account.

Because the source and destination servers are the same, the

permissions should be valid. Click Next.

11. On the Select Databases page make sure the Copy checkbox is

enabled for the CopyMe database. All other checkboxes should

be cleared. (Note that in the Status column for the CopyMe

database that it sees the database already exists. That’s all right;

you’ll copy it to a new name). Click Next.

12. On the Configure Destination Database (1 of 1) page, review and

leave everything at their default values. Notice that the wizard

has automatically named the copy CopyMe_new to make sure the

copy doesn’t overwrite the original database. Review the paths of

the data files as well. Select Stop the transfer if a database or file

with the same name exists at the destination. Click Next.

13. On the Configure the Package page, leave the package name

unchanged, but remember it so that you can look it up later.

Turn on the Save transfer logs? checkbox. Select Text file from

the drop down menu, and leave the log path at the default

(record the path so that you can look at the log after the copy).

Click Next.

14. On the Schedule the Package page, leave the option set to Run

immediately. Even though you are running it immediately, it will

still create a SQL Server Agent Job to run the copy for you. Leave

the Integration Services Proxy Account set to SQL Server Agent

Service Account. Click Next.

15. On the Complete the Wizard page, review the list of actions and

then click Finish.

16. On the Performing operation page, watch as the package is

created and run. If all is well, each item in the list should

complete with Success.

17. Back in the SSMS Object explorer, right click the Databases node

and choose Refresh. You should now see a CopyMe_new data-

base in the list.

18. Expand the CopyMe_new database and then expand Tables. You

should see the dbo.Product table in the list of tables. Right-click

the dbo.Product table and select Select Top 1000 Rows. Review

the data in the query window that opens. Close the query

window when you are done.

19. In Object Explorer under your SQL Server instance, Expand SQL

Server Agent and then expand Jobs. You should see a job listed

with the same name as the package (you were supposed to have

remembered that name back in step 13). This is the SQL Server

Agent Job that got created to run the package. Open the job and

review its details, but don’t change anything.

20. In the Object Explorer, from the Connect menu, select Integration

Services…. Enter the name of your SQL Server Instance for the

Server name: and click Connect.

21. Expand Stored Packages | MSDB | <YOUR INSTANCE NAME> | DTS

Packages | Copy Database Wizard Packages. You should see a

package there with the name you recorded in step 13. This is the

actual package. It has been saved into the MSDB database on

your local instance. Later, you’ll open the package up in BIDS to

see what is inside it.

Using the Import and Export Wizard

While the Copy Database Wizard copies an entire database, the Import and

Export Wizard provides you with a more granular copy mechanism. The Copy

Database Wizard was also limited to using only SQL Server as both the source and

destination. The Import and Export Wizard, however, can connect to most any

database engine as either the source or the destination.

The Import and Export Wizard allows you to export data from the source

database by either selecting the specific tables and views you want to export, or by