solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips













































   Got a SQL tip?
            We want to know!

Move data between SQL Server database filegroups

MSSQLTips author Mohammed Moinudheen By:   |   Read Comments (7)   |   Related Tips: More > Database Administration
Problem

As per our business requirements, we are planning on archiving some historical data from large tables into a separate filegroup and make that filegroup read only. What are the options that are available for moving data in tables to a separate filegroup? Can you describe the steps for changing the filegroup option to read only? Check out this tip to learn more.

Solution

Since we cannot move data files between filegroups, it is a common practice to move data for a table between filegroups in a database. This excerpt from SQL Server Books Online says it all: "Because the leaf level of a clustered index and the data pages are the same by definition, creating a clustered index and using the ON partition_scheme_name or ON filegroup_name clause effectively moves a table from the filegroup on which the table was created to the new partition scheme or filegroup." (Source - http://msdn.microsoft.com/en-us/library/ms188783.aspx)

This is a straight forward method of moving data from a table from one filegroup to another. We can recreate\create the clustered index on the new filegroup thereby moving the data from one filegroup to another. There are some options that are generally followed and we shall discuss them here with some examples. Moving the data could be accomplished either through SQL Server Management Studio or via scripts. Sample scripts could be used as shown below to verify the data movement.

Let's create a sample database with three filegroups, then create a table with a clustered index to use in this tip's examples. Check out the script below:

/* Create a test database with 3 filegroups */
USE master;
GO
CREATE DATABASE Test
ON PRIMARY (NAME = Test_data1, FILENAME = 'C:\Test_data1.mdf'),
FILEGROUP Test_Secondary
(NAME = Test_data2, FILENAME = 'C:\Test_data2.ndf'),
FILEGROUP Test_Archive 
(NAME = Test_data3, FILENAME = 'C:\Test_data3.ndf')
LOG ON(NAME = Test_log, FILENAME = 'C:\Test_log.ldf')
GO
/* Create a sample table and then create a clustered index */
USE Test
GO
CREATE TABLE dbo.employee
(emp_id int,
emp_fname varchar(10),
emp_lname varchar(10)) on test_secondary
GO
CREATE CLUSTERED INDEX CIX_emp_Id on Test.dbo.employee(emp_id)
GO

When we run the sp_helpindex system stored procedure on the employee table, we would see the clustered index is on the test_secondary filegroup:

using sql server mangement studio

Option 1 - Using SQL Server Management Studio to change the filegroup for the clustered index

Using SQL Server Management Studio (SSMS), we could navigate (Root| Databases | Database Name i.e. Test | Tables | Table Name i.e. dbo.Employee | Indexes) to the path as shown in screenshot below to move the index from one filegroup to another.

using ssms to move the index from one filegroup to another

On right click on the index and choose the Properties option. In the Properties window, select the Storage tab as shown below and select the destination filegroup.

in the properties window, select the storage tab

Once you click OK, the index will be moved, so be careful working through these steps on a production system when users are online. After running, sp_helpindex system stored procedure, we can verify the filegroup has changed as shown.

verify the filegroup has changed

Option 2 - Using T-SQL scripts to change the filegroup for the clustered index

To accomplish the same task as outlined in option 1 with SQL Server Management Studio, we just need to use the script below to create the clustered index on the new filegroup.

CREATE CLUSTERED INDEX CIX_emp_Id ON Test.dbo.employee(emp_id) 
WITH(DROP_EXISTING=ON,Online=ON) on [Test_Archive]
GO

In this script, we use the Online option to minimize the user impact during index creation as the clustered index creation in the offline mode would prevent user access to the table. In option 1, that is via SSMS, there is an option in the Index property->Storage tab, to allow online index creation. For that, the checkbox "Allow online processing of DML statements while moving the index", needs to be checked. This is possible only if we select "Rebuild Index" in the Options Tab of the Index property window.


Moving data with unique or primary key constraints on a table

In the two options listed above there were no constraints on the example table. However, in the real world they exist and I am not confident we would be able to use the steps outlined in option 1 with SSMS if the indexes were created by a unique or primary key constraint. Consider the sample table below:

USE Test
GO
CREATE TABLE dbo.employee1
(emp_id int constraint pk_emp_id primary key,
emp_fname varchar(10),
emp_lname varchar(10)) on test_secondary
GO

In this example, the primary key has been created on the dbo.employee1 table, which creates a unique clustered index as shown in screenshot below.

using t-sql scripts to change the filegroup

Even if we navigate in to the same screen in SSMS as shown in option 1 above, the option to move the indexes to different filegroup is disabled. In this case, we would need to use a similar script as used in option 2 to create the index on the new filegroup.

CREATE UNIQUE CLUSTERED INDEX pk_emp_id ON Test.dbo.employee1(emp_id) 
WITH(DROP_EXISTING=ON,Online=ON) on [Test_Archive]
GO

Running the code above would move the index as shown in the screen shot below.

create unique clustered index

Determine the SQL Server objects per filegroup

It is a good idea to have a script handy in order to figure out on which objects reside in a particular filegroup in a database. There are some useful scripts in another tip, in the "How can I determine which objects exist in a particular filegroup?" section. The code below has been modified to fit our example.

/*Script for finding on which Filegroup the object resides in a database*/
USE Test
GO
SELECT object_name(i.[object_id]) as Name_of_Object,
i.name as Index_Name,
i.type_desc as Index_Type,
f.name as Name_of_Filegroup,
a.type as Object_Type,
f.type,
f.type_desc
FROM sys.filegroups as f 
INNER JOIN sys.indexes as i 
 ON f.data_space_id = i.data_space_id
INNER JOIN sys.all_objects as a 
 ON i.object_id = a.object_id
WHERE a.type ='U' -- User defined tables only
AND object_name(i.[object_id]) ='employee' -- Specific object
GO

Sample output as shown.

determine the sql server objects per filegroup

Of course, we could collect this information through SSMS but it would be easier for us to gather this data using scripts.


Configuring a SQL Server filegroup as read only

As a final step let's configure the filegroup as read only. Check out the script below and be sure you have exclusive access to the database before running the code.

ALTER DATABASE Test MODIFY FILEGROUP Test_Archive READ_ONLY
GO
Next Steps
  • With the examples in this tip, we can see how data can be moved from one filegroup to another as well as configure the filegroup for Read_Only mode. Taking these steps could protects the data from accidental loss and would meet your requirement of archiving historical data.
  • Be sure to write your scripts carefully and test your code prior to running it in a production environment.
  • Be careful running this code on a production system, may want to consider running this code during a maintenance window so the users are not impacted.
  • Familiarize yourself with the different options available to move data from one filegroup to another based on your table design.
  • Analyze the data completely before deciding to archive it into a separate filegroup.


Last Update: 7/22/2011


About the author
MSSQLTips author Mohammed Moinudheen
Mohammed Moinudheen is a SQL Server DBA with over 6 years experience managing production databases for a few Fortune 500 companies.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Comments and Feedback:
Wednesday, July 27, 2011 - 6:25:13 PM - Shiv Read The Tip

Good job!!!!! Its a really helpful article


Thursday, August 11, 2011 - 8:59:13 AM - Scott Read The Tip

Perhaps I'm missing something, but I don't see how this meets the needs as described in the "Problem" section.  All you've done is moved the table's storage from one physical locaion to another.  The table still has the same name.  Any applications referencing the table will still perform the same insert/update/delete actions against it.  And if you mark the new filegroup as read-only, then those applications will fail.  How does this archive the data?  It seems to me that this will just make a table in your database unusable.


Thursday, August 11, 2011 - 10:27:29 AM - Mohammed Moinudheen Read The Tip

Scott, Thanks for your comments. I think I should have rephrased the problem statement better. I was just referring  to historical data assuming that they are no longer required to be modified by the application. Thanks for pointing out though.


Thursday, December 29, 2011 - 2:29:46 AM - MAnish Read The Tip

Gud Job done. We got alll the info at one place in simple and descriptive manner.

 


Sunday, March 03, 2013 - 10:05:50 PM - unruledboy Read The Tip

Why the "Allow online processing of DML statements while moving the index" is grayed out, even I check the "Rebuild index" option?


Friday, March 15, 2013 - 11:14:52 AM - Rob Read The Tip

unruledboy, the Allow online processing is only available if your are running Enterprise or Developer's Editions of SQL Server, (there might be other versions based upon the version you are running).


Monday, April 28, 2014 - 3:44:50 AM - Rajasekhar Read The Tip

Hi Could it be possible to move the table with in the file group from one file to another file?Or Can we distribute the data atleast between two files?



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.