LOB_DATA : varbinary(max),varchar(max), image , text columns.
I have 4 tables with large amount of LOB_DATA in it. I would like to move LOB_DATA in these tables to different File group in a different hard disk.
I have created a new filegroup and file in this filegroup. I also used sql command from this question. But varbinary column storage remains where it was before.
-- To move table data to new filegroup CREATE UNIQUE CLUSTERED INDEX PK_YourTableName ON dbo.YourTableName(YourPKFields) WITH (DROP_EXISTING = ON) ON [NewFilegroup]
I use following sql to see filegroup of lob_data.
-- To see lob_data filegroups SELECT OBJECT_NAME(object_id) as OBJECT_NAME, FILEGROUP_NAME(data_space_id) as FILE_GROUP_NAME, type_desc FROM sys.partitions p JOIN sys.allocation_units a on p.partition_id = a.container_id WHERE type_desc = 'LOB_DATA'
I am using SQL Server 2008.
I have found following solution how-to-move-the-lob-data-from-one-file-group-to-other. Do other approaches exists?