Microsoft® SQL Server® 2008 Bible

Chapter 44. Transferring Databases

IN THIS CHAPTER

• Using the Copy Database Wizard

• Generating SQL scripts

• Detaching and attaching databases

• Using the Import and Export Wizard

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 41, "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 44-1 lists the copy requirements and the various methods of moving a database.

Copy Database Wizard

The Copy Database Wizard actually generates a SQL Server Integration Services (SSIS) package that can copy or move one or more databases from one server to another. If the database is being 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 2008 to an older version of SQL Server. In addition, both source and destination servers must have the SQL Server Agent running (it is stopped by default when working with ...