Moving text/LOB data to different filegroup
-
I'm working on an general purpose script to move all DB objects from one filegroup to another. Am almost done, now have a script that will reliably move tables and all indexes from one filegroup to another. Most of the time this can be done "online" with no obvious outage, and so far I've not had to drop/recreate tables to accomplish any of this.
Only thing remaining is text/LOB data. I don't see any way to move this to a different filegroup without dropping and re-creating the table.
Anyone know if how or if it's even possible to move text/LOB data to a different filegroup without dropping the table?
-
There is no way to do this without dropping and re-creating the table. There are scripts that will aid you in doing that, but I had to do the same thing a little while back, and couldn't find it. I will be
if I find out that it can be done, but for me at least, it was fairly painless. Luckily for me there was only one table that was of any size that amounted in much of an outage. -
Thanks, Bob. I figured as much, just shot in the dark to see if anyone had figured out a way (maybe found some undocumented functionality).
If anyone with input to MS reads this.... IMHO I think this is missing feature that needs to be added. With SQL 2005, it became much easier to move tables and indexes between filegroups. Now this can generally be done online and quickly, and can be done without actually having to drop & recreate the table. UNLESS the table contains text/LOB data, in which case all that goes out the window & you have to create a new table. Text/LOB was not all that common in years past, but now with the ease of use offered by varchar(MAX), I'm seeing it more and more. In a DB I just reviewed, 25% of tables had a varchar(MAX) column. Just need to add a bit of optional syntax to the ALTER TABLE stmt I think?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply