SQL Server™ 2005 Bible

Chapter 35. Transferring Databases

In This Chapter

• Using the Copy Database Wizard

• Generating SQL scripts

• Detaching and attaching databases

Transferring data may be a mundane task, but SQL Server databases are often developed on one server and deployed on other servers. Without a reliable and efficient method of moving database schemas and whole databases, the project won't get very far.

SQL Server enables multiple means of moving databases. As a database developer or database administrator (DBA), you should have basic skills in the following topics, three of which are covered in this chapter:

• Copy Database Wizard

• SQL scripts

• Detach/attach

• Backup/restore (covered in Chapter 36, "Recovery Planning")

The keys to determining the best way to move a database are knowing how much of it needs to be moved and whether or not the servers are directly connected by a fast network. Table 35-1 lists the copy requirements and the various methods of moving a database.

Table 35-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	No	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-Defined Error Messages	Yes	Depends on the script	Yes	Yes

Copy Database Wizard

The Copy Database ...