How to set up filegrouping in existing Database 2

[appi]

Hi all,

I need to set up Filegroups in an existing database which has onle one primary file by now.
In case that this a production DB, and I started with MS-SQL Server without deeper knowledge (i am an Oracle and Informix DBA) I need a safe way to create filegroups to gain security and performance issues.

kind regards
Uwe

 

[yelworcm]

Look up the "Alter Database" command in BOL. It should show you everything you need to know. If I recall correctly, it is:

alter database mydb add filegroup filegroupname

The major difference from Oracle is that you can have an empty filegroup, where in Oracle (I think) you need to define a file with the tablespace.

 

[Catadmin]

Hey, Yelworcm,

A couple of quick question from someone who hasn't needed to do this before. Have you done this before?

If so, is it possible to create the new filegroup and then horizontally partition the existing database across the filegroup OR is it possible to split off some of the tables of the database and put them on the new filegroup?

For some reason, I've got it in my head that adding a filegroup to an existing DB only allows you to put new data on the filegroup, not move old data onto it. Especially if the database only had one filegroup before creating the new one.

Thanks!

Catadmin


Catadmin - MCDBA, MCSA
"The only stupid question is the one you *didn't* ask.

 

[SQLDenis]

a fast way to move a table is to rebuild the clustered index (on new filegroup) this will move the whole table to that filegroup

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[yelworcm]

Yes, much as SQLDenis has said, you can rebuild indexes on the new filegroup, or move tables to the new filegroup in a couple of ways. bcp out/in is another way, but may incur more downtime.

 

[yetanotherjo]

Is this a way to get around the db size limitations when using MSDE?

 

[mrdenny]

No. (You should have posted this as a new thread, not in an existing thread).

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[yetanotherjo]

Ta.

 

[Catadmin]

SQLDenis & Yelworcm: You da bomb!

Stars for your assistance!! I'd send you cookies (chocolate chip), but a lot of browsers don't seem to like them these days. @=)

Catadmin



Catadmin - MCDBA, MCSA
"The only stupid question is the one you *didn't* ask.