Data Splitting: MDF & NDF Files

Question

Post reply

Data Splitting: MDF & NDF Files

Dave Lavers-279505

SSC Enthusiast

Points: 164
More actions
May 5, 2006 at 12:24 pm

#114532

I have a database with mdf & ldf files only.
I want to create an ndf file on a new drive.
Is there a way to split the current data between the mdf and the ndf files.
I have been using DBSS Shrinkfile without success.
Thanks,
Dave

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Mark Yelton SSCommitted Points: 1570 More actions · Answer 1

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.

Good Luck,

Mark

John Rowan SSC Guru Points: 56440 More actions · Answer 2

You can split the data out using filegroups. I think you have to drop and recreate the tables you want to move. EM allows you to do this, but I would guess that, if you ran profiler, EM would be dropping and recreating the table also. Look up Filegroups>>switching filegroup for table in BOL.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Dave Lavers-279505 SSC Enthusiast Points: 164 More actions · Answer 3

DBSS dbreindex appears to move the indexes to the ndf.

I guess that I will need to use filegroups to move data (table by table).

I appreciate all of your help with this.

Thanks,

Dave

Eddie Wuerch SSChampion Points: 12535 More actions · Answer 4

To move a table from one filegroup to another, re-create the clustered index on the target filegroup:

-- SQL 2000 syntax:
CREATE CLUSTERED INDEX [name] ON [table](column, column, ...)
  WITH (DROP_EXISTING [,other options...]) 
    ON [target filegroup]

-- SQL 2005 syntax (new syntax underlined):
CREATE CLUSTERED INDEX [name] ON [table](column, column, ...)
  WITH (DROP_EXISTING = ON [,other options...]) 
    ON [target filegroup]

...then do the same to your non-clustered indexes. By using the DROP_EXISTING option, you can execute CREATE INDEX statements against existing indexes without explicitly dropping them first.

If any of your tables don't have clustered indexes, then one will be created for them when you do this. Although this is also usually the preferred way to go (especially when choosing the right clustered key), you can always drop the clutered index after the move.

-Eddie

Eddie Wuerch
MCM: SQL