619
Chapter 23: Transferring Databases
23
On the Confi gure Destination Database screen the wizard asks you to confi gure the destina-
tion database. You modify the destination database name and modify the default locations
for the database fi les on the destination server. You also specify how the Copy Database
Wizard reacts when a database with the requested name already exists on the destination.
The options are either stopping the transfer or dropping the existing database and creating
a new database with the same name. The wizard moves all the objects and data.At the Select Server Objects screen you can optionally direct the wizard to move the
following:■ (^) All logins or only those that have access to the database
■ All or selected nonsystem stored procedures in the Master database used by the
database
■ All or selected SQL Agent jobs (automated and optionally scheduled tasks)
■ (^) All or selected endpoints
■ All or selected SSIS packages
■ (^) All or selected user-defi ned error messages (used by the raiserror T-SQL
command)
Click the ellipsis button next to each selected option and choose individual objects you
want to transfer. If you do not choose individual objects, all the objects of each selection
option are transferred by default.
The Confi gure the Package screen shows the location of the Integration Services pack-
age generated by this process. You can also provide a name for the package and choose a
method for logging errors. The default method uses the Windows event log. You can also
send a list of errors to a text fi le. The wizard requests a fi lename when you choose this
option.
The Schedule the Package directs the wizard to either run the Integration Services package
once upon completion of the wizard, run it once later, or set it up on a regular schedule.
The fi nal screen shows a summary of choices that were made so far. If you want to change
something, you can click the Back button to revisit your selections. If you are satisfi ed
with all the choices, click Finish.
When fi nished, the wizard generates and runs (if you selected the option to run
immediately) the Integration Services package on the destination server. If you selected
the option to schedule the package, it is saved and scheduled on the destination server.
You can open the generated Integration Services job by selecting the Jobs node under SQL
Server Agent (of destination SQL Server) in the console tree and then double-clicking on
the package. If the name was not edited in the wizard’s schedule page, the name should
be CDW followed by the two server names and an integer. The creation date is also listed.
c23.indd 619c23.indd 619 7/31/2012 9:25:21 AM7/31/2012 9:25:21 AM
http://www.it-ebooks.info