It can be especially frustrating if you
don??™t catch the mistake immediately. Filegroups require a bit of up-front work, but the payoff in the long
run can be significant.
Moving Objects to Different Filegroups
The easiest way to move objects is to update the property page for that table or index.
However, moving objects en masse this way isn??™t possible??”you??™d need a T-SQL script for
that. If you run a SQL Server Profiler trace while making such a change via SQL Server
Management Studio, you??™ll see that SQL Server actually creates a second table on the new
filegroup, inserts all of the existing data into that new table, drops the original table, and
then changes the new table name to the original object name, as shown in Figure 4-6.
CHAPTER 4 n BACKING UP AND RESTORING FILES AND FILEGROUPS 83
Figure 4-6. Example SQL Server Profiler output from tracing the movement of objects from
one filegroup to another
Luckily, the movement illustrated in Figure 4-6 is a set of logged actions, so if any one
of the steps fails, the whole process will fail. That means that moving filegroups will cause
the transaction log to grow suddenly.
Pages:
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184