When you need to transfer (move) databases between two SQL Servers or make
a copy of a database on the same SQL Server, you would be using the Transfer
Database Task. For migrating databases from an earlier version (SQL Server 2000) to
SQL version 2005 as well, you would need this task, though there are other methods
available for the transfer.
There are two ways in which databases can be transferred using the Transfer
Database Task: the online and offline modes.
In the online mode, the transfer takes place through the mediation of the SQL
Management Object (SMO) connections.
In the offline mode, the transfer takes place by detaching and attaching the database
files, which render the database unusable during the transfer, and also requiring
SMO connections. Also, in order to successfully transfer a database from one server
to another, you must have proper permissions for the databases, as well as files and
folders on the two computers.
Package that Transfers a Database from One SQL Server to Another
[ 228 ]
The Transfer Database Task also permits overwriting a destination database with
the same name replacing the contents. There are some restrictions on the size of the
database i.e. to be transferred relative to the size of the model database, as discussed
in this online informative article, http://msdn2.microsoft.com/en-us/library/
ms141204.aspx, which describes this task in greater detail.
Further, the Transfer Database Task component in SSIS is error prone as it assumes
only one installed SQL Server, which may not be the case in general.
Pages:
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163