Table Location

[faisul]

Hi Guys,

Is it possible to change the location of a 'table' to another disk. A software developer has proposed this change and i'm not sure on how this is possible.

Thanks
FN

 

[JayKusch]

You could create another SQL Server file group and move the needed table into that new created group. The new file group may be place on another disc for better performance and backup needs.

Thanks

J. Kusch

 

[faisul]

Thanks very much for the reply : J. Kusch

can you suggest and document or walkthrough that would give me more info abouting setting this up..

thnaks in advance
fn

 

[JayKusch]

Here ya go ... Second topic of the article is what I beleive you are looking for:

http://www.sqljunkies.com/How To/B9F7F302-964A-4825-9246-6143A8681900.scuk

Thanks

J. Kusch

 

[JayKusch]

The short of it would be to add the new file group to the existing DB as so:

ALTER DATABASE Test1 
ADD FILE 
(
 NAME = Test1dat2,
 FILENAME = 'C:\SQL\Data\t1dat2.ndf',
 SIZE = 5MB,
 MAXSIZE = 100MB,
 FILEGROWTH = 5MB
)
GO

Then you would move the existing table to the new filegroup by something like (straight from BOL):

How to place an existing table on a different filegroup (Enterprise Manager)
To place an existing table on a different filegroup 

Expand a server group, and then expand a server.

Expand Databases, expand the database in which the table belongs, and then click Tables.

In the details pane, right-click the table, and then click Design Table.

Right-click any column, and then click Properties.

On the Tables tab, in the Table Filegroup list, select the filegroup on which to place the table.

Optionally, in the Text Filegroup list, select a filegroup on which to place any text, image, and ntext columns. 

See Also Placing Tables on Filegroups

Thanks

J. Kusch

 

[faisul]

THANKS FOR ALL THE HELP!!!

J. Kusch