MSSQLWIKI

Karthick P.K on SQL Server

How to move the LOB data from one file group to other?

Posted by Karthick P.K on January 17, 2012

 

We do not have a direct way to move the LOB data from one file group to other. Using ALTER TABLE and/or CREATE INDEX to support moving LOB data is unavailable till current version of  SQL (SQL Server 2008).

 

Only way to move the LOB data is to

1. create new table in new file group

2. Move the data from existing table to new table.

3. Drop the existing table.

4. Change the name of new table to Old table.

Management studio has easy way to create script for all the above task.

1. In management studio Right click the table –>Design –>change the file group in properties windows (Click View—> properties window if you do not see properties window)

2. Generate Change Script.

clip_image002

3. Script similar to following script is generated.

4. Copy the script and run in Query window.

 

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/

BEGIN TRANSACTION

SET QUOTED_IDENTIFIER ON

SET ARITHABORT ON

SET NUMERIC_ROUNDABORT OFF

SET CONCAT_NULL_YIELDS_NULL ON

SET ANSI_NULLS ON

SET ANSI_PADDING ON

SET ANSI_WARNINGS ON

COMMIT

BEGIN TRANSACTION

GO

CREATE TABLE dbo.Tmp_BLOB_TABLE

                (

                BLOBName varchar(100) NULL,

                BLOBData varbinary(MAX) NULL

                )  ON [PRIMARY]

                TEXTIMAGE_ON Lob2

GO

ALTER TABLE dbo.Tmp_BLOB_TABLE SET (LOCK_ESCALATION = TABLE)

GO

IF EXISTS(SELECT * FROM dbo.BLOB_TABLE)

                EXEC(‘INSERT INTO dbo.Tmp_BLOB_TABLE (BLOBName, BLOBData)

                                SELECT BLOBName, BLOBData FROM dbo.BLOB_TABLE WITH (HOLDLOCK TABLOCKX)’)

GO

DROP TABLE dbo.BLOB_TABLE

GO

EXECUTE sp_rename N’dbo.Tmp_BLOB_TABLE’, N’BLOB_TABLE’, ‘OBJECT’

GO

COMMIT

 

Thanks

Karthick P.K

About these ads

6 Responses to “How to move the LOB data from one file group to other?”

  1. Prashant said

    Nice post Karthick :)
    I am little confused with this statement though:
    “Using ALTER TABLE and/or CREATE INDEX to support moving LOB data is unavailable till current version of SQL (SQL Server 2008).”

    SQL 2008 onwards, can we move the LOB data to a different filegroup just by recreating indexes on the destination filegroup (without dropping table etc) ?

    • Hello Prashant,

      “Using ALTER TABLE and/or CREATE INDEX” to move LOB data is unavailable till current version of SQL (SQL Server 2008/2011 builds released till date). This option would most likely come in future.

      Thanks
      Karthick

      • Prashant said

        ok that is clear now :)

        For now, is there any other workaround so that we avoid dropping the table (and re-create etc) in order to move LOB data to a diff filegroup?

  2. sql user said

    Thank you Kartick, very useful information about this limitation, but how about other dependent objects, like indexes, statistics, triggers, foreign keys and other dependent objects on the table. I suppose every thing needs to be created manually and if we want to move multiple tables to another file groups, then it will become very complicated, especially with foreign keys.

    Is there is request raised with product team already on this? is there a connect item on this

  3. obviously like your web-site however you need to test the spelling on quite a few of your posts.
    Several of them are rife with spelling problems and I in
    finding it very bothersome to inform the
    reality on the other hand I’ll definitely come again again.

  4. […] How to move the LOB data from one file group to other? […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 2,107 other followers

%d bloggers like this: