| Author |
Splitting existing database into multiple files groups
|
|
| tolcis 2006-11-15, 7:14 pm |
| Hi!
I have an existing database wich I want to split into 2 or 3 separate
file groups and put them on different physical drives.
Can anybody point me to the right direction on how to do the split?
I would like to be able to move tables and/or indexes.
Thank you.
Tol
| |
| David Browne 2006-11-15, 7:14 pm |
|
"tolcis" <a.liberchuk@verizon.net> wrote in message
news:1163602405.069610.189080@b28g2000cwb.googlegroups.com...
> Hi!
>
> I have an existing database wich I want to split into 2 or 3 separate
> file groups and put them on different physical drives.
> Can anybody point me to the right direction on how to do the split?
> I would like to be able to move tables and/or indexes.
>
Placing Indexes on Filegroups
http://msdn2.microsoft.com/en-us/library/ms190433.aspx
But if you just want to spread the data and IO across multiple drives, you
can simply add additional files to your primary file group. SQL Server will
spread the data and the load, effectively striping across the volumes.
David
| |
| Tibor Karaszi 2006-11-15, 7:14 pm |
| You can relocate an existing index using CREATE INDEX and the DROP EXISTING clause (which Books
Online for details).
For a table that don't have a clustered index, I suggest that you create a clustered index on the
desired filegroup, and then remove the clustered index.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
"tolcis" <a.liberchuk@verizon.net> wrote in message
news:1163602405.069610.189080@b28g2000cwb.googlegroups.com...
> Hi!
>
> I have an existing database wich I want to split into 2 or 3 separate
> file groups and put them on different physical drives.
> Can anybody point me to the right direction on how to do the split?
> I would like to be able to move tables and/or indexes.
>
> Thank you.
>
> Tol
>
| |
|
| What you need to do first is create those file group using Alter database
command and then add file to those group
ALTER DATABASE XX
ADD FILEGROUP SECOND_GROUP
GO
ALTER DATABASE XX
ADD FILE
(
NAME = SECONDARY,
FILENAME = 'L:\XX_ix1.ndf',
SIZE = 5MB,
FILEGROWTH = 5MB
)
TO FILEGROUP SECOND_GROUP
to move table to different group you can either use table design view
screen->table and index property window or alter table command
read filegroup in BOL for more info
vt
"tolcis" <a.liberchuk@verizon.net> wrote in message
news:1163602405.069610.189080@b28g2000cwb.googlegroups.com...
> Hi!
>
> I have an existing database wich I want to split into 2 or 3 separate
> file groups and put them on different physical drives.
> Can anybody point me to the right direction on how to do the split?
> I would like to be able to move tables and/or indexes.
>
> Thank you.
>
> Tol
>
|
|
|
|