617
Chapter 23: Transferring Databases
23
destination server’s SQL Server Agent is not running, the wizard will prompt you to start
it. The Copy Database Wizard offers the most fl exibility and capability. The wizard offers
you one of two choices: The detach and attach method or the SQL Management Object
(SMO) method.The detach and attach method may be faster but requires the database to go offl ine because the database is being
removed from the existing instance. You should be extremely careful with this method for upgrade purposes because
this method is a “one-way” process. If you detach a database from a lower version of SQL Server ( e.g. 2008 R2) and
attach it to a higher level such as SQL Server 2012, upon attachment to the new instance, the database gets inter-
nally upgraded to be compatible with the new version. Due to this automatic upgrade process, the database fi les can
no longer be attached to a lower-level version. Before you complete any upgrade or migration process make sure you
get good full backups of your databases so you can roll back in case there are any issues.You access the Copy Database Wizard by right-clicking the database you want to copy and
choosing Tasks ➪ Copy Database from the context menu. Skip past the Welcome to the Copy
Database Wizard page by clicking Next.For more information about starting and stopping SQL Server Agent, refer to Chapter 3, “Installing SQL
Server.”On the fi rst screen (Select a Source Server) and second screen (Select a Destination Server)
the Copy Database Wizard begins by gathering the name of the source and destination
servers and the required security information to log into the server.On the third screen (Select the Transfer Method), as shown in Figure 23-1, the wizard asks
how you want to transfer the database. Using the detach and attach method is faster, but
it requires that SQL Server have additional rights to both source and destination databases,
and you must allow exclusive access to both. The detach and attach method works best
for large databases. The SQL Management Object (SMO) method doesn’t require any spe-
cial access, and users can continue using the source database, but the user can still have
issues with rights so be sure you have suffi cient rights to access all objects from the source
database. However, this method is signifi cantly slower and is not recommended for large
databases.On the Select Databases, screen, as shown in Figure 23-2, the wizard enables you
to select the databases you want to move or copy. The status column lets you know
if it is OK to move the database and explains if the database cannot be moved. For
example, system databases (master, msdb, model and tempdb) cannot be moved or
copied.c23.indd 617c23.indd 617 7/31/2012 9:25:20 AM7/31/2012 9:25:20 AM
http://www.it-ebooks.info