Moving a Set of Tables to a Read-Only Filegroup

This is my first post.  My goal with this post is to share how I moved a subset of the tables in my database to another filegroup.  Why would you want to do this, you ask?  A few possible use-cases:

• You want to move a set of tables to a different drive
• You like playing with SQL Server Files and Filegroups
• You want to move some tables out-of a database, but are not ready to drop them from their source DB.

The third option was my reason for researching it, but obviously you may have other reasons yet.

My overall approach to the problem above was to:

1. Create a new filegroup
2. Create a file in that filegroup
3. Move the tables, online where possible (SQL Server Enterprise is required), to the new filegroup
4. Flip that filegroup to READ_ONLY mode… unfortunately, I found out while doing this that this step requires that nobody else be in the database… I ultimately flipped the database to SINGLE_USER mode after-hours.  This may not be an easy option for you.
5. Using SSMS and the Import/Export Data Wizard (SSIS under-the-covers), I copied the data to its new destination where I had earlier prepared empty tables

I was only moving a sub-set of the tables… luckily for me, they all had the same prefix in their table name, so it was easy for me to grab them programmatically.  It’s important to note that running my script doesn’t do anything to your database… rather it generates TSQL commands for you to review and, optionally, run.

The Issue

I wanted to move the tables… every part of them.  I wanted my code to account for heaps, clustered indexes, and non-clustered indexes.  I found some very useful links (included before) which did part of what I wanted… but not everything.  I also wanted to accommodate as many index options as possible (and not lose settings like fill factor, filters, and ALLOW_TABLE_LOCK options).  This is my first attempt, and I’ve tried to note exceptions to what I’ve tested.

The Approach

I’m sure I’ll catch a lot of flack for the iterative nature of my script… I’d normally be the first one to offer criticism!  As it turns out, it’s very difficult to build a set of things into a single string (eg, building a set of index columns into a command).  I found a few alternatives (including a very clever use of the STUFF() function and some XML generation… but I felt that was unintuitive and it seemed “hacky” to me.  In the end, I make heavy use of WHILE loops.  In TSQL.  I know.  Please feel free to disagree with me on this, but this is the choice I made in the end.

The Demo Setup

CREATE DATABASE FileGroupTesting;
GO
USE FileGroupTesting;

--Heap
CREATE TABLE [aHeap](id int, name nvarchar(50))
--Heap w/NC
CREATE TABLE [aHeapWithNCs](id int, name nvarchar(50))
CREATE NONCLUSTERED INDEX [HeapsNC] ON [aHeapWithNCs](name desc)
--Heap w/PK
CREATE TABLE [heapWithPK](id int primary key NONCLUSTERED, name nvarchar(50))
--Clustered no PK
CREATE TABLE [ClusteredNoPK] (id int, name nvarchar(50))
CREATE CLUSTERED INDEX [PK_ClusteredNoPK] ON [ClusteredNoPK](id)
--Clustered no PK with NCs
CREATE TABLE [ClusteredNoPK2] (id int, name nvarchar(50))
CREATE CLUSTERED INDEX [PK_ClusteredNoPK2] ON [ClusteredNoPK2](id)
CREATE NONCLUSTERED INDEX [ClusteredNoPKNC] ON [ClusteredNoPK2] (name desc)

--Clustered with PK
CREATE TABLE [ClusteredPK] (id int primary key clustered, name nvarchar(50))

--Clustered with PK with NCs
CREATE TABLE [ClusteredPK2] (id int primary key clustered, name nvarchar(50))
CREATE NONCLUSTERED INDEX [IX_ClusteredPK2] ON [ClusteredPK2] (name, id desc)

--Heap w/Unique
CREATE TABLE [HeapWithUnique](id int , name nvarchar(50))
ALTER TABLE [HeapWithUnique] ADD CONSTRAINT  [UC_HeapWithUnique] UNIQUE NONCLUSTERED(name)
CREATE UNIQUE NONCLUSTERED INDEX [UC_HeapWithUnique2] ON [HeapWithUnique] (name)

--PK/Clustered w/Unique
CREATE TABLE [ClustWithUnique](id int , name nvarchar(50))
ALTER TABLE [ClustWithUnique] ADD CONSTRAINT [UC_ClustWithUnique] UNIQUE NONCLUSTERED(name)
CREATE UNIQUE NONCLUSTERED INDEX [UC_ClustWithUnique2] ON [ClustWithUnique] (name)

--PK/Clustered w/filter
CREATE TABLE [ClusteredFilter] (id int primary key clustered, name nvarchar(50))
CREATE NONCLUSTERED index [ix_clusteredfilter] on [ClusteredFilter](name)  WHERE Name > 'M'

--PK/clustered w/included
CREATE TABLE [ClusteredIncluded] (id int primary key clustered, name nvarchar(50), isActive bit)
CREATE NONCLUSTERED index [IncludedInd] on [ClusteredIncluded] (name) include(isActive)

--PK/clustered w/desc
CREATE TABLE [ClusteredDesc] (id int not null, name nvarchar(50))
ALTER TABLE [ClusteredDesc] ADD CONSTRAINT [DescConstraint] PRIMARY KEY CLUSTERED (ID desc)

--Clustered with two cols
CREATE TABLE [ClusteredWithTwoCols] (id int not null, name nvarchar(50) not null)
ALTER TABLE [ClusteredWithTwoCols] ADD CONSTRAINT [PK_ClusteredWithTwoCols] PRIMARY KEY CLUSTERED (ID, name desc)

--Heap with an uncool name
CREATE TABLE [Table With A Bad Name] (id int)

Check Current Filegroup Layout

Of course, as we didn’t specify any filegroup options, we have SQL’s default: a single filegroup called [PRIMARY] with everything on it.  We can verify this by running the following query:

SELECT
	 fg.name			AS		[FileGroup]
	,fg.is_read_only
	,s.name				AS		[Schema]
	,o.name				AS		[TABLE]
	,i.index_id
	,i.name				AS		[Index]
	,i.[type]
	,i.type_desc			AS		[IndexType]
FROM sys.objects o
INNER JOIN [sys].[schemas] [s]
	ON [o].[schema_id]=[s].[schema_id]
INNER JOIN [sys].[indexes] [i]
	ON [o].[object_id]=[i].[object_id]
INNER JOIN [sys].[filegroups] [fg]
	ON [i].[data_space_id]=[fg].[data_space_id]
WHERE 1=1
	AND [o].[type]='U'
	AND [o].[is_ms_shipped]=0
	--AND [s].[name]='dbo'
	--AND [o].[name] LIKE 'TABLEPrefix%'
ORDER BY [fg].[name], [s].[name], [o].[name], [i].[index_id]

Let’s start by setting up our new filegroup and creating a file in it:

ALTER DATABASE [FileGroupTesting] ADD FILEGROUP [FGTest]
--Be sure to replace the file path below with a valid directory on your system!
ALTER DATABASE [FileGroupTesting]
ADD FILE
( NAME = FileGroupTest,
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\FileGroupTest.ndf'
)
TO FILEGROUP [FGTest]

The Solution

Next, we can use the script below (after changing to the appropriate database context and modifying any of the “setable” parameters.

/*
* Author: Ryan Snee
* Version: 1.0
* Date 20131015
*
* License: This work is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.
* License Details: http://creativecommons.org/licenses/by-sa/3.0/deed.en_US
*
* LIMITATIONS
* If an index is partitioned, it comes off the partition to go be put onto a filegroup
* Not sure how compression is handled... I assume we lose our compression settings, as they are per index, per partition
* Statistics_norecompute setting is defaulted back to OFF (should rarely be ON anyway, but something to be aware of)
* Assumes that no heap has a column called [TempPK]
* Does not account for ColumnStore Indexes
* Not tested with indexed views

Does handle:
 Online Index Rebuilds, where possible
 Filtered Indexes

*/

SET NOCOUNT ON;
DECLARE @LoopSafetyBefore int,
@LoopSafetyAfter int,

@LoopSafetyInternalBefore int,
@LoopSafetyInternalAfter int,
@Execute bit,
@CurrentObject int,
@CurrentIndex int,
@IndexColID int,
@colListBase nvarchar(max),
@colListInclude nvarchar(max),
@cmd nvarchar(max),
@TryOnlineRebuild bit,
@NewFG sysname,
@TableFilter sysname

--
set @NewFG='FGTest'
set @TryOnlineRebuild=1
set @TableFilter='%'
--

------------------
--1) Prep work----
------------------

	if object_id('tempdb..#Indexes') is not null
	drop table #Indexes

	if object_id('tempdb..#IndexDetail') is not null
	drop table #IndexDetail

	CREATE TABLE #Indexes (
		ID int IDENTITY(1,1) PRIMARY KEY CLUSTERED ,
		SchemaName sysname,
		ObjectName sysname,
		ObjectID int,
		IndexName sysname null,
		IndexID int,
		IndexType int,
		Is_Unique bit,
		Is_Primary bit,
		Type_Desc nvarchar(60),
		Is_Padded bit,
		Ignore_Dup_Key bit,
		Allow_Row_Locks bit,
		Allow_Page_Locks bit,
		Fill_Factor tinyint,
		isDisabled bit,
		FileGroupName sysname,
		Has_Filter bit,
		Filter_Definition nvarchar(max),
		--key_ordinal tinyint
		LOBColumns int,
		isDone bit,
		Cmd nvarchar(max)

	)

	CREATE TABLE #IndexDetail (
		ID int IDENTITY(1,1) PRIMARY KEY CLUSTERED ,
		SchemaName sysname,
		ObjectName sysname,
		ObjectID int,
		IndexName sysname null,
		IndexID int,
		IndexType int,
		Is_Unique bit,
		Is_Primary bit,
		Type_Desc nvarchar(60),
		Is_Padded bit,
		Ignore_Dup_Key bit,
		Allow_Row_Locks bit,
		Allow_Page_Locks bit,
		Fill_Factor tinyint,
		isDisabled bit,
		Is_Descending_Key bit,
		ColumnName sysname null,
		Is_Included_Column bit,
		FileGroupName sysname,
		Has_Filter bit,
		Filter_Definition nvarchar(max),
		key_ordinal tinyint,
		isDone bit default(0)
	)

	--First I get info on all of the indexes we need to move
	--This includes the columns that make up the index, if applicable
	--The set includes 1 row per index per column it includes
	INSERT INTO #IndexDetail
	SELECT	 s.name as [SchemaName]
			,o.Name as ObjectName
			,o.object_id as ObjectID
			,si.Name as IndexName
			,si.index_id
			,si.type
			,si.Is_Unique
			,si.is_primary_key
			,si.Type_Desc
			,si.Is_Padded
			,si.Ignore_Dup_Key
			,si.Allow_Row_Locks
			,si.Allow_Page_Locks
			,si.Fill_Factor
			,si.is_disabled
			,sic.Is_Descending_Key
			,sc.Name as ColumnName
			,sic.Is_Included_Column
			,sf.Name as FileGroupName
			,si.has_filter as Has_Filter
			,si.filter_definition as Filter_Definition
			,sic.key_ordinal
			,0 as [isDone]
	 FROM 
		sys.Objects o
		INNER JOIN sys.Indexes si
			ON o.Object_Id = si.Object_id
		left join sys.schemas s
		on o.schema_id=s.schema_id
		INNER JOIN sys.FileGroups sf
			ON sf.Data_Space_Id = si.Data_Space_Id
		LEFT JOIN  sys.Index_columns sic
			ON si.Object_Id = sic.Object_Id
				AND si.Index_id = sic.Index_id
		LEFT JOIN  sys.Columns sc
			ON sic.Column_Id = sc.Column_Id
				and sc.Object_Id = sic.Object_Id 
	 WHERE 
		o.type='U'
		and o.is_ms_shipped=0
		AND o.Name LIKE COALESCE(@TableFilter, '%')
	 ORDER BY ObjectName, IndexName, sic.Key_Ordinal

	 --From the #IndexDetail table, I extract a set containing just 1 row per index
	INSERT INTO #Indexes 
	(SchemaName, ObjectName, ObjectID, IndexID, IndexName, IndexType, is_Unique, is_primary,Type_Desc, is_padded, ignore_dup_key, allow_row_locks,allow_page_locks,fill_factor, isDisabled, FileGroupName, has_filter,filter_definition, LOBColumns, isDone, Cmd)
	SELECT DISTINCT
	 SchemaName, ObjectName, ObjectID, IndexID, IndexName, IndexType, is_Unique, is_primary,Type_Desc, is_padded, ignore_dup_key, allow_row_locks,allow_page_locks,fill_factor, isDisabled, FileGroupName,has_filter,filter_definition, 0, 0, N''
	FROM #IndexDetail;

	--Find indexes that include LOB data
	--Calculate how many LOB columns each index has and write it to the LobColumns column of #Indexes
	WITH IndexesWithLOBs AS (

		SELECT ObjectID, IndexID, COUNT(*) AS LOBColumns
		FROM #Indexes i
			LEFT JOIN sys.index_columns ic --Grabbing INCLUDEd columns
				on i.ObjectID=ic.object_id
				AND i.IndexID=ic.index_id
			left join sys.columns c
				on i.ObjectID=c.object_id
				AND ic.column_id=c.column_id
			left join sys.types t
				on c.system_type_id=t.user_type_id
		WHERE	1=1
				AND i.Type_Desc='NONCLUSTERED'
				and ((
						t.system_type_id IN (231, 165, 167)-- nvarchar, varbinary, varchar
							AND c.max_length = -1 -- (MAX)
					)
					OR t.system_type_id IN (35, 34, 241, 99)-- text, image, xml, ntext
				)

		GROUP BY ObjectID, INDEXID

		UNION ALL

		SELECT ObjectID, IndexID, count(*) as LobColumns
		FROM #Indexes i
			left join sys.columns c --Grabbing columns that are part of the index
				on i.ObjectID=c.object_id
			left join sys.types t
				on c.system_type_id=t.user_type_id
		WHERE	1=1
				AND (i.Type_Desc='CLUSTERED' OR i.Type_Desc='HEAP')
				and ((
						t.system_type_id IN (231, 165, 167)--nvarchar, varbinary, varchar
							AND c.max_length = -1-- (MAX)
					)
					OR t.system_type_id IN (35, 34, 241, 99)-- text, image, xml, ntext
				)
		GROUP BY ObjectID, IndexID

	)

	UPDATE I
	SET i.LobColumns=LOB.LobColumns
	FROM #Indexes i
		INNER JOIN IndexesWithLOBs LOB
			ON i.ObjectID=LOB.ObjectID
				AND i.IndexID=LOB.IndexID

------------------------
--2) Process the Heaps--
------------------------
	UPDATE #Indexes
		SET [Cmd]=
			'ALTER TABLE '+QUOTENAME(SchemaName)+'.'+QUOTENAME(ObjectName) +' ADD [TempPK] bigint identity(1,1);'
			+' CREATE CLUSTERED INDEX [IX_Temp] ON '+QUOTENAME(SchemaName)+'.'+QUOTENAME(ObjectName) +' ([TempPK] ASC) WITH(ONLINE='
			+CASE WHEN (@TryOnlineRebuild=1 AND LOBColumns=0 AND isDisabled=0)
				THEN 'ON'
				ELSE 'OFF'
			END
			+') ON ['+@NewFG+'];'
			+'DROP INDEX [IX_Temp] ON '+QUOTENAME(SchemaName)+'.'+QUOTENAME(ObjectName)+' WITH(ONLINE='
			+CASE WHEN (@TryOnlineRebuild=1 AND LOBColumns=0)
				THEN 'ON'
				ELSE 'OFF'
			END
			+');'
			+'alter table '+QUOTENAME(SchemaName)+'.'+QUOTENAME(ObjectName)+' drop column [TempPK];'
			--Handle when Page Locks were disabled
			+CASE
				WHEN [Allow_Page_Locks]=0
				THEN ' ALTER INDEX ALL ON '+QUOTENAME(SchemaName)+'.'+QUOTENAME(ObjectName) +' SET  (ALLOW_Page_LOCKS = OFF );'+char(13)+char(10)
				ELSE ''
				END
			--Handle when Row Locks were disabled
			+CASE
				WHEN [Allow_Row_Locks]=0
				THEN ' ALTER INDEX ALL ON '+QUOTENAME(SchemaName)+'.'+QUOTENAME(ObjectName) +' SET  (ALLOW_Row_LOCKS = OFF ); '
				ELSE ''
			END
		,isDone=1
	WHERE Type_Desc='Heap'
		AND isDone=0

	--Assumption: Padding, ignore_duplicate, fillfactor, is_disabled, and filtering don't apply to a heap 

------------------------------------------
--3) Process the Nonclustered Indexes-----
------------------------------------------
	WHILE EXISTS (SELECT ID FROM #Indexes WHERE Type_Desc='NONCLUSTERED' AND isDone=0)
	BEGIN

		SET @CurrentObject= NULL
		SET @CurrentIndex= NULL

		select @LoopSafetyBefore=count(*) FROM #Indexes WHERE Type_Desc='NONCLUSTERED' AND isDone=0;

		--Grab One Index
		SELECT TOP 1 @CurrentObject=ObjectID, @CurrentIndex=IndexID FROM #Indexes WHERE Type_Desc='NONCLUSTERED' AND isDone=0;

		set @colListBase=''
		set @colListInclude=''

		--For each NC Index, loop through every column in the core index to build up @colListBase
		WHILE EXISTS (SELECT 1 FROM #IndexDetail WHERE isDone=0 and Is_Included_Column=0 AND ObjectID=@CurrentObject AND IndexID=@CurrentIndex )
		BEGIN
			select @LoopSafetyInternalBefore=count(*) FROM #IndexDetail WHERE isDone=0 and Is_Included_Column=0 and ObjectID=@CurrentObject and IndexID=@CurrentIndex;

			SELECT TOP 1 @IndexColID=ID FROM #IndexDetail WHERE isDone=0  and Is_Included_Column=0 AND ObjectID=@CurrentObject AND IndexID=@CurrentIndex ORDER BY key_ordinal ASC

			if @LoopSafetyInternalBefore=1
				BEGIN
					select @colListBase=@colListBase+quotename(ColumnName) + CASE WHEN Is_Descending_Key=1 THEN ' DESC' ELSE '' END FROM #IndexDetail WHERE ID=@IndexColID
				END
			ELSE
				BEGIN
					select @colListBase=@colListBase+quotename(ColumnName) + CASE WHEN Is_Descending_Key=1 THEN ' DESC, ' ELSE ', ' END FROM #IndexDetail WHERE ID=@IndexColID
				END

			UPDATE #IndexDetail SET isDone=1 WHERE ID=@IndexColID

			select @LoopSafetyInternalAfter=count(*) FROM #IndexDetail WHERE isDone=0 and Is_Included_Column=0 and ObjectID=@CurrentObject and IndexID=@CurrentIndex;
			if(@LoopSafetyInternalBefore<=@LoopSafetyInternalAfter)
			BEGIN
				RAISERROR('Infinite Loop while adding columns for object %i',11,1, @CurrentObject) WITH NOWAIT
				BREAK
			END

		END

		--Then Loop through the included columns to build up @colListInclude
		WHILE EXISTS (SELECT 1 FROM #IndexDetail WHERE isDone=0 and Is_Included_Column=1 AND ObjectID=@CurrentObject AND IndexID=@CurrentIndex )
		BEGIN

			select @LoopSafetyInternalBefore=count(*) FROM #IndexDetail WHERE isDone=0 and Is_Included_Column=1 and ObjectID=@CurrentObject and IndexID=@CurrentIndex;

			SELECT TOP 1 @IndexColID=ID FROM #IndexDetail WHERE isDone=0  and Is_Included_Column=1 AND ObjectID=@CurrentObject AND IndexID=@CurrentIndex

			if @LoopSafetyInternalBefore=1
				BEGIN
					select @colListInclude=@colListInclude+quotename(ColumnName) FROM #IndexDetail WHERE ID=@IndexColID
				END
			ELSE
				BEGIN
					select @colListInclude=@colListInclude+quotename(ColumnName)+', ' FROM #IndexDetail WHERE ID=@IndexColID
				END

			UPDATE #IndexDetail SET isDone=1 WHERE ID=@IndexColID

			select @LoopSafetyInternalAfter=count(*) FROM #IndexDetail WHERE isDone=0 and Is_Included_Column=1 and ObjectID=@CurrentObject and IndexID=@CurrentIndex;

			if(@LoopSafetyInternalBefore<=@LoopSafetyInternalAfter) 			BEGIN 				RAISERROR('Infinite Loop while adding included columns for object %i',11,1, @CurrentObject) WITH NOWAIT 				BREAK 			END 		 		END 	 		--Now that the column lists are unrolled, form the command 		SELECT @cmd= 			'CREATE ' 			+CASE 				WHEN Is_Unique=1 					THEN 'UNIQUE ' 				ELSE '' 			END 			+'NONCLUSTERED INDEX '+quotename(IndexName) +' ON '+QUOTENAME(schemaname)+'.'+QUOTENAME(ObjectName) 			+'('+@colListBase+')' 			+CASE WHEN len(@colListInclude)>0
				THEN 'INCLUDE('+@colListInclude+')'
				ELSE ''
			END
			+CASE WHEN Filter_Definition is not null
				THEN ' WHERE '+Filter_Definition
				ELSE ''
			END
			+' WITH(DROP_EXISTING=ON,PAD_INDEX='
			+CASE
				WHEN is_padded=1
					THEN 'ON,'
				ELSE 'OFF,'
			END
			+CASE
				WHEN Fill_Factor>0
					THEN 'FILLFACTOR='+cast(fill_factor as varchar(5))+','
				ELSE ''
			END
			+'IGNORE_DUP_KEY='
			+CASE
				WHEN Ignore_Dup_Key=1
					THEN 'ON,'
				ELSE 'OFF,'
			END

			+'ALLOW_ROW_LOCKS='
			+CASE
				WHEN Allow_Row_Locks=1
					THEN 'ON,'
				ELSE 'OFF,'
			END
			+'ALLOW_PAGE_LOCKS='
			+CASE
				WHEN Allow_Page_Locks=1
					THEN 'ON,'
				ELSE 'OFF,'
			END
			+'ONLINE='
			+CASE
				WHEN (@TryOnlineRebuild=1 AND LOBColumns=0 AND isDisabled=0)
					THEN 'ON'
				ELSE 'OFF'
			END
			+') ON '+quotename(@NewFG)+';'
			+CASE
				WHEN isDisabled=1
					THEN ' ALTER INDEX '+quotename(IndexName)+' ON '+QUOTENAME(SchemaName)+'.'+QUOTENAME(ObjectName)+' DISABLE;'
				ELSE ''
			END

		FROM #Indexes
		WHERE ObjectID=@CurrentObject AND IndexID=@CurrentIndex

		--Write the command back to the table
		UPDATE #Indexes
		SET [Cmd]=@cmd, isDone=1
		WHERE ObjectID=@CurrentObject
			AND IndexID=@CurrentIndex

		SELECT @LoopSafetyAfter=count(*)
		FROM #Indexes
		WHERE Type_Desc='NONCLUSTERED'
			AND isDone=0;

		if(@LoopSafetyBefore<=@LoopSafetyAfter)
		BEGIN
			RAISERROR('Infinite Loop in Indexes, object %i',11,2,@CurrentObject) WITH NOWAIT
			BREAK
		END

	END

----------------------------------------------------
--4) Repeat the pattern for Clustered Indexes-------
----------------------------------------------------

	WHILE EXISTS (SELECT ID FROM #Indexes WHERE Type_Desc='CLUSTERED' AND isDone=0)
	BEGIN

		SET @CurrentObject= NULL
		SET @CurrentIndex= NULL

		select @LoopSafetyBefore=count(*) FROM #Indexes WHERE Type_Desc='CLUSTERED' AND isDone=0;

		--Grab One Index
		SELECT TOP 1 @CurrentObject=ObjectID, @CurrentIndex=IndexID FROM #Indexes WHERE Type_Desc='CLUSTERED' AND isDone=0;

		set @colListBase=''
		set @colListInclude=''

		--For each Clustered Index, loop through every column in the core index
		WHILE EXISTS (SELECT 1 FROM #IndexDetail WHERE isDone=0 AND ObjectID=@CurrentObject AND IndexID=@CurrentIndex )
		BEGIN

			select @LoopSafetyInternalBefore=count(*) FROM #IndexDetail WHERE isDone=0 and ObjectID=@CurrentObject and IndexID=@CurrentIndex;

			SELECT TOP 1 @IndexColID=ID FROM #IndexDetail WHERE isDone=0  and ObjectID=@CurrentObject AND IndexID=@CurrentIndex ORDER BY key_ordinal ASC

			if @LoopSafetyInternalBefore=1
				BEGIN
					select @colListBase=@colListBase+quotename(ColumnName) + CASE WHEN Is_Descending_Key=1 THEN ' DESC' ELSE '' END FROM #IndexDetail WHERE ID=@IndexColID
				END
			ELSE
				BEGIN
					select @colListBase=@colListBase+quotename(ColumnName) + CASE WHEN Is_Descending_Key=1 THEN ' DESC, ' ELSE ', ' END FROM #IndexDetail WHERE ID=@IndexColID
				END

			UPDATE #IndexDetail SET isDone=1 WHERE ID=@IndexColID

			select @LoopSafetyInternalAfter=count(*) FROM #IndexDetail WHERE isDone=0 and ObjectID=@CurrentObject and IndexID=@CurrentIndex;

			if(@LoopSafetyInternalBefore<=@LoopSafetyInternalAfter) 			BEGIN 				RAISERROR('Infinite Loop while adding columns for object %i',11,1, @CurrentObject) WITH NOWAIT 				BREAK 			END 		 		END 	 		--Form the command 		SELECT 			@cmd= 'CREATE ' 			+CASE 				WHEN Is_Unique=1 					THEN 'UNIQUE ' 				ELSE '' 			END 			+'CLUSTERED INDEX '+quotename(IndexName) +' ON '+QUOTENAME(schemaname)+'.'+QUOTENAME(ObjectName) 			+'('+@colListBase+')' 			+' WITH(DROP_EXISTING=ON,PAD_INDEX=' 			+CASE WHEN is_padded=1 				THEN 'ON,' 					ELSE 'OFF,' 			END 			+CASE 				WHEN Fill_Factor>0
					THEN 'FILLFACTOR='+cast(fill_factor as varchar(5))+','
				ELSE ''
			END
			+'IGNORE_DUP_KEY='
			+CASE
				WHEN Ignore_Dup_Key=1
					THEN 'ON,'
				ELSE 'OFF,'
			END
			+'ALLOW_ROW_LOCKS='
			+CASE
				WHEN Allow_Row_Locks=1
					THEN 'ON,'
				ELSE 'OFF,'
			END
			+'ALLOW_PAGE_LOCKS='
			+CASE
				WHEN Allow_Page_Locks=1
					THEN 'ON,'
				ELSE 'OFF,'
			END
			+'ONLINE='
			+CASE
				WHEN (@TryOnlineRebuild=1 AND LOBColumns=0 AND isDisabled=0)
					THEN 'ON'
				ELSE 'OFF'
			END
			+') ON '+quotename(@NewFG)+';'
			+CASE
				WHEN isDisabled=1
					THEN ' ALTER INDEX '+quotename(IndexName)+' ON '+QUOTENAME(SchemaName)+'.'+QUOTENAME(ObjectName)+' DISABLE;'
				ELSE ''
			END	
		FROM #Indexes
		WHERE ObjectID=@CurrentObject AND IndexID=@CurrentIndex

		--Write the command back to the @Indexes table
		UPDATE #Indexes
		SET [Cmd]=@cmd, isDone=1
		WHERE ObjectID=@CurrentObject
		AND IndexID=@CurrentIndex

		select @LoopSafetyAfter=count(*) FROM #Indexes WHERE Type_Desc='CLUSTERED' AND isDone=0;

		if(@LoopSafetyBefore<=@LoopSafetyAfter)
		BEGIN
			RAISERROR('Infinite Loop in Indexes, object %i',11,2,@CurrentObject) WITH NOWAIT
			BREAK
		END

	END

--select * from #IndexDetail
--select * FROM #Indexes

SELECT
	FileGroupName
	,SchemaName
	,ObjectName
	,IndexName
	,IndexID
	,IndexType
	,Is_Unique
	,is_Primary
	,Type_Desc
	,Is_Padded
	,Ignore_Dup_Key
	,Allow_Row_Locks
	,Allow_Page_Locks
	,Fill_Factor
	,isDisabled
	,Has_Filter
	,Filter_Definition
	,LOBColumns
	,[Cmd]
FROM #Indexes
WHERE FileGroupName <> @NewFG
ORDER BY SchemaName, ObjectName

Remember, the script doesn’t change anything; rather it generates TSQL commands you can choose to run.

Running the Generated Commands and Verification

The final step would be to review and possibly run the commands in the last column of the table.

After doing so, we can run the query mentioned in the “Check Current Filegroup Layout” section above to verify that every index of every (user) table has moved to the new filegroup/file.

As a note, system tables are forever in the first, primary data file.  It can be moved (an offline operation, unfortunately), but it cannot be removed.  Attempting to do so will yield the following error:

Msg 5020, Level 16, State 1, Line 1
The primary data or log file cannot be removed from a database.

Changing the Filegroup to Read_Only

Now that my objective of moving a subset of the user tables to a new filegroup has been accomplished, I can continue on to step 4 of my approach:

use FileGroupTesting;

--You need to be the only one in the database.  You may need to kick everyone else off:
alter database [FileGroupTesting] set single_user with rollback immediate;

--Make FileGroup ReadOnly
ALTER DATABASE [FileGroupTesting]
MODIFY FILEGROUP [FGTest] Read_only;

alter database [FileGroupTesting] set multi_user with rollback immediate;

SELECT
name
,is_read_only
,is_default
FROM sys.filegroups

The full script can be found here: MovingTablesToNewFG.txt.

That’s it! Please let me know if you found this script useful!

LLAP,
Ryan

A few sources I’d like to cite for contributing ideas:

• http://stackoverflow.com/questions/4237238/moving-all-non-clustered-indexes-to-another-filegroup-in-sql-server
• http://dba.stackexchange.com/questions/28872/move-primary-key-to-filegroup-sql-server-2012
• http://www.aaronlowe.net/archive/2007/09/move-heap-tables/

---

The text of this entire blog site (including all scripts) is under the copyright of Ryan Snee.You are free to reproduce it, as outlined by the Creative Commons Attribution-ShareAlike 3.0 Unported License.