Microsoft SQL Server 2012 Bible

Chapter 23

Transferring Databases

In This Chapter

Using the Copy Database Wizard

Generating SQL Scripts

Detaching and Attaching Databases

Using the Import and Export Wizard

Creating and Deploying a Data-Tier Application (DAC)

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 to move database schemas and whole databases, the project can't go far.

What's New in SQL Server Configuration?

sp_attach_db is a deprecated feature and will be removed in future versions of SQL Server. It is recommended to use CREATE DATABASE database_name FOR ATTACH instead.

For a full detailed listing of all features being deprecated in this, and future, releases of SQL Server see http://msdn.microsoft.com/en-us/library/ms143729.aspx.

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

• Copy Database Wizard
• SQL scripts
• Detach/Attach
• Backup/Restore (covered in Chapter 21, “Backup and Recovery Planning”)
• Data-Tier Application

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

The preceding ...