How to add secondary file group in sql server2000

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

How to add secondary file group in sql server2000

Author

Topic

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2006-12-05 : 06:14:13

Can any body let me know that How can I add secondary file group in my existing database {with data} in sql server2000.
I can't find any option in sql server enterprise manager to add secondary filegroup.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-05 : 06:24:03

In the database properties dialog box in Enterprise Manager, fourth tab from the left. Click on the empty row, type in the new filegroup name and Bingo...You have added new filegroup. Now start adding data files to the newly created filegroup.

Harsh Athalye
India.
"Nothing is Impossible"

TonyTheDBA
Posting Yak Master

121 Posts

Posted - 2006-12-05 : 07:19:27

Copy and Paste this into Query Analyser

ALTER DATABASE <db_name, Sysname , DBName>
  ADD FILE ( NAME       = <logical_file_name , , DBCurrName>_<data_or_log , dat|log , dat>
           , FILENAME   = '<OS_Drive, , D:><OS_Path, , \data\><db_name, Sysname , DBName>\<OS_Filename, ,Filename.ndf>'
           , FILEGROWTH = <filegrowth , 128 | 10 , 128><suffix, % | KB | MB | GB | TB , MB>  
           , SIZE       = <size , , 512><Size_suffix , KB | MB | GB | TB , MB>
           , MAXSIZE    = <maxsize , 512|UNLIMITED , 512><Size_suffix , KB | MB | GB | TB , MB>
           ) TO FILEGROUP <DB_Filegroup, , [PRIMARY]>

Presh <CTRL>+<SHFT>+<M> and fill in the appropriate values for your database.

Remember "The GUI is evil! Query Analyser is your friend"

--
Regards
Tony The DBA

sponguru_dba
Yak Posting Veteran

93 Posts

Posted - 2006-12-05 : 07:22:21

Hi

1.Create File group
ALTER DATABASE <Database name> ADD FILEGROUP <desire filegroupname>

2.Add the Data file to that group
Alter database <Db_name> add file
(name=<Data file name>,filename='c:\<ndfdatafile_name>.ndf',size=1Mb,maxsize=2mb,filegrowth=1mb)
to filegroup <Created file group name>

Cheers...

SreenivasaRao
Bangalore,INDIA

TonyTheDBA
Posting Yak Master

121 Posts

Posted - 2006-12-05 : 07:24:31

Doh! Oh Bother! Copied the wrong script fragment! Try this

ALTER DATABASE <db_name, Sysname , DBName>
  ADD FILEGROUP <filegroupname , , NewFilegroup>

to create the filegroup, followed by the other fragment to add files to the filegroup

--
Regards
Tony The DBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources