Chapter 4. Transferring Your Database to Other Systems

After completing this chapter, you will be able to:

• Use backup and restore techniques to move your data

• Detach, copy, and re-attach your database to move the data

• Understand when replication may be the best option

• Use SQL Server Integration Services (SSIS) to move tables and databases

• Schedule jobs to automate data movement between servers

So, you need to have your data in more than one place. You might distribute your data for performance or to provide for disaster recovery. Maybe you have a corporate center that needs to distribute data to regional or local offices. There can be many reasons to move your data.

When determining the scope of your data distribution needs, you should consider factors including:

• Latency How often does the data change? How often do changes need to be uploaded to the recipients?

• Editing Who needs to be able to modify the data? Do the modifications need to be merged together?

• Sharing Do you need to send only a segment of the data or the entire database?

• Security Is the data sensitive?

In the previous chapter, you learned how to use T-SQL to back up and restore databases. In this chapter, you will learn about four methods within SQL Server 2005, including backing up and restoring, for transferring your data. Each method has its strengths and weaknesses, and each is suited to a particular type of data transfer. The four methods are:

• Backup and Restore

• Detach and Attach

• Replication

• SQL Server Integration Services (SSIS)

Some of these methods require running wizards, which we will cover as well. SSIS and SQL Server Agent can be used to schedule and automate the method that you choose; this technique is covered in the last section of this chapter.

As we go through these methods, the term "target" will be used in reference to the system to which you are moving data. The term "source" will be used to refer to the system that is the source of the data you are transferring. All of the examples in this chapter will use the AdventureWorks database as the source running on the default instance of SQL Server 2005. The target server will be a named instance, "target," on the same server. We will be using various targets to illustrate the options for transferring data based on different requirements.

Converting Your Database to SQL Server 2005 from SQL Server 2000

The following methods for transferring data can be used to upgrade a database by copying it to a server running SQL Server 2005:

• Backup and Restore

• Detach and Attach

• Copy Database Wizard

Before you venture down the upgrade path, run the Microsoft SQL Server 2005 Upgrade Advisor and review the SQL Server 2005 Upgrade Handbook. Both of these resources are available as free downloads on www.microsoft.com/sql.