Our ERP server is windows2003+MS-SQL+BAANLN.
The database file .mdf set to automatically grow and with unrestricted file growth. Now the .mdf file grow to 80G.
My question is how to split the big .mdf file to several small one in MS-SQL server?
Please tell me the steps.
Thanks a lot

Probably http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=56434 should help u .

you can use mssql filegroups http://www.databasejournal.com/features/mssql/article.php/1439801
so you also have NDF files. I once looked into this so that I could backup my archive and live company seperately in sql. the idea being I would do a dump of the archive company create a new filegroup. Via sql make this new ndf file the default for all new tables then restore archive company dump back to sql which would place all the archive tables into the new NDF file and then change the default back to the mdf file.

Hi,
though I was searching for how to speed up my BDBPRE process, I saw you problem. I have step wise procedure in my documents. I tried to search it for u but could not. I used it on our test server and it splitted my MDF into small MDFs which can be stored on any partition of harddisk. Give me some time. If I get it I should give u.
Bye

EXAMPLE:

1 - To create new datafile and filegroup to store tables/indexes
of a new 567 company (Transac-SQL) :

/* Create a new "fg567" filegroup : */
ALTER DATABASE baandb ADD FILEGROUP fg567
GO

/* Verifiy It's OK : */
sp_helpfilegroup
GO

/* Add a datafile to this new "fg567" filegroup : */
ALTER DATABASE baandb ADD FILE
(
NAME = data567,
FILENAME = 'E:\msql7\Data\data567.NDF',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB
)
TO FILEGROUP fg567
GO
Note: You need to mention correct filename path
/* Verifiy It's OK : */
sp_helpfilegroup

2 - To modify the msql_storage file :
Initial content :
*:*:T:group:011:5:
*:*:I:group:011:5:

After having added the relevant lines :
*:567:T:group:011:5:FILEGROUP fg567
*:567:I:group:011:5:FILEGROUP fg567
*:*:T:group:011:5:
*:*:I:group:011:5:

3 - To create table for company 567
Restart your Baan session, open GTM (ttaad4100) and create table tccom000
for the new 567 company.
Check that the newly created table is assigned to filegroup fg567 :
(Using Transc-SQL) :
dbcc checkfilegroup ('fg567')
This should display table ttccom000567