After completing this chapter, you will be able to:
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:
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:
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.