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
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.
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 ...