Microsoft® SQL Server® 2012 Bible

616

Part V: Enterprise Data Management

The keys to determining the best way to move a database are knowing how much of it

needs to be moved and whether the servers are directly connected by a fast network. Table

23-1 lists the copy requirements and the various methods to move a database.

TABLE 23-1 Database Transfer Methods

Requirement

Copy Database

Wizard SQL Scripts

Detaching

Attaching Backup Restore

Exclusive Access to the

Database

Yes No Yes No

Copies Between

Disconnected Servers

No Yes Yes Yes

Copies Database

Schema

Yes Yes Yes Yes

Copies Data Yes Depends on

the script

Yes Yes

Copies Security Server logins,

database users,

security roles,

and

permissions

Depends on

the script

Database

users, security

roles, and

permissions

Database

users,

security

roles, and

permissions

Copies Jobs/User-

Defi ned Error

Messages

Yes De pend s on

the script

Yes Yes

The preceding four methods are good for moving databases from one SQL Server

to another. But on many occasions a DBA needs to quickly move a few tables from

one database to another or copy data from a non-SQL Server database (for example,

Microsoft Offi ce Excel or a fl at fi le) to SQL Server or vice versa. To accomplish this,

Microsoft provides the Import and Export Wizard to quickly copy data from one source

to another.

Copy Database Wizard

The Copy Database Wizard generates a SQL Server Integration Services (SSIS) package

that can copy or move one or more databases from one server to another. If the data-

base is moved to a server on the same network server, this is the premiere method. This

method won’t work to copy a database from SQL Server 2012 to an older version of SQL

Server. In addition, destination server must have the SQL Server Agent running. If the

c23.indd 616c23.indd 616 7/31/2012 9:25:20 AM7/31/2012 9:25:20 AM

http://www.it-ebooks.info