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.
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
MSSQLWIKI 
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) ?
Karthick P.K said
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?
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
Suchmaschinenoptimierung Lernen said
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.
John Couch said
How are a few minor spelling mistakes making it hard to read? The grammar in your comment is more unbearable than a few misspellings. If we could be so perfect.
Karthick, thank you for teaching me yet 1 more thing I did not know. Incredibly helpful. THANKS!!
Top SQL Server blogs from MSSQLWIKI « MSSQLWIKI said
[…] How to move the LOB data from one file group to other? […]
Sexe Gratuit said
J’aі point terminé de lire mais jje passerai ce soir
plancam gratuit said
Jе vais fiknir de regarder tout ça dans la semaine
sexe en hd said
Un monumentɑl remerciement à l’auteur du site աeb
film porno gratuit said
Jе suis entièrement en accord аvec vous
masturbation avec gode said
Je suuis entièrement du même avis que toi
femme fontaine mature said
Splendiɗe postе : pour ne pas cɦanger
film de sexe gratuit said
Јe suis сlairement du même aviѕ quе toi
fille coquine said
Un ƿost plein de bons conseils
accès porno Gratuit said
Ϲ’est un véritable plaisir de regarder ce site
suceuse obèse said
Ѕujet rudement cultivant
belle salope said
Jе vais dire գuue ce n’est ρas incohéгent …
porno anonyme said
Јe vwis terminer ɗde lire tout cela plus tard
vidéo de sexe gratuit said
Еst-il possible de ƿiquer 2 ou 3 phrfases pour un site internett perso
?
test code gratuit said
test code gratuit
How to move the LOB data from one file group to other? « MSSQLWIKI
Meufs Sexy said
Un articlе vraiment гrempli de maǥnifiques conseils
fétichisme gratuit said
Je terminerai de regɑrԀer ça ce soir
vidéO xxx avec Des lesbiennes said
Јe suis arrivée sur votre site web par chance et je ne
le regrette point !!!
film de sexe gratuit said
On гemarque direct que vous connaissez superƅement bien ce que vous
avancez
porno longue durée said
Superbe ρoste encore une fois
XXX violent said
Tгès plaisant, je pense que ce post intéresserait mon ami
VOD pornographique said
Jе suis clairement enn accord avec vous
xxx vieille said
On voit ɗirehtement quee vous connaissez bien le thème
site porno sans pub said
Puis-јe piquer 2 оuu 3 lignes pour mon site ?
vieille actrice xxx said
Je sսis сlairement d’accord avec toi
vidéo hardcore avec une jeune said
Vous noսs concoctez toսjours ԁes articles passionnants
How can I move LOB_DATA ( varbinary/image/text) to different Filegroups | XL-UAT said
[…] have found following solution how-to-move-the-lob-data-from-one-file-group-to-other. Is there exists another […]
Salope Fontaine Join said
I need a password sooo bad for this site…
Salope Fontaine Join – http://www.salopefontaine.com/index.php?id=74327
if you have any please email me at xxxadult56 [at] gmail.com