Changing number of data files

I have a 225Gb database on a test server. It is currently in one file and I
would like to split it into several files smaller files. What is the best
way of doing this?
thanks
GavGav
CREATE DATABASE mywind
GO
ALTER DATABASE mywind ADD FILEGROUP new_customers
ALTER DATABASE mywind ADD FILEGROUP sales
GO
ALTER DATABASE mywind ADD FILE
(NAME='mywind_data_1',
FILENAME='d:\mw.dat1')
TO FILEGROUP new_customers
ALTER DATABASE mywind
ADD FILE
(NAME='mywind_data_2',
FILENAME='d:\mw.dat2')
TO FILEGROUP sales
"Gav" <gavin.metcalfe@.portakabinnospam.com> wrote in message
news:bu3072$ltb$1@.titan.btinternet.com...
> I have a 225Gb database on a test server. It is currently in one file and
I
> would like to split it into several files smaller files. What is the best
> way of doing this?
> thanks
> Gav
>|||Hello,
Uri's solution will work, however you will have to drop
and re-create tables to add data to the filegroups.
This is because when you create a table (or index) you
also state which filegroup you will be saving your data to.
J
>--Original Message--
>I have a 225Gb database on a test server. It is currently
in one file and I
>would like to split it into several files smaller files.
What is the best
>way of doing this?
>thanks
>Gav
>
>.
>|||If I create another data file is it possible to move data from one file to
the other? I currently have a 220Gb and a 50Gb file I would like to fill up
the 50Gb file so I can resize the 220Gb file. Unfortunately I don't have
much spare storage space so I'm not sure how else I would do it. Only other
thing I can think of doing is dropping the entire database and recreating it
with more files, then I could restore a backup version.
Gav
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:09fc01c3da8a$b5e91800$a301280a@.phx.gbl...
> Hello,
> Uri's solution will work, however you will have to drop
> and re-create tables to add data to the filegroups.
> This is because when you create a table (or index) you
> also state which filegroup you will be saving your data to.
> J
> >--Original Message--
> >I have a 225Gb database on a test server. It is currently
> in one file and I
> >would like to split it into several files smaller files.
> What is the best
> >way of doing this?
> >
> >thanks
> >
> >Gav
> >
> >
> >.
> >|||Yes, but that sort of depends where your data file is. for
instance you can create a data file on a different drive /
server than the one your using now, but if you create it
on a different server you will need to watch your network
speed.
The problem is not going to be your data files as such,
but filling them.
When you create a table/index you explicitly say
in T-SQL which data file any data the table is going to
store saves to.
Creating extra data files does will not move the data.
I don't think (and please someone tell me if I'm wrong)
whether a backup will work as a backup has in it the
database stucture so if you restore it you also restore
the number of datafiles you had before i.e 1.
There is a way of saving some time, and that it to do with
indexes.
About 1 third of all storeage space is used by indexes
(both clustered and non clusted) so hat you could do is
BACKUP YOUR DATABASE
(Sorry bit paraniod about data)
1. Create a new datafile called indexes.
2. Under EM Database - all tasks - generate SQL
Create 2 scripts one to remove all indexes and primary keys
one to re create them.
3. Drop all you indexes
4. In the script that has the create for the indexes
perform a search and replace for all the old data file
name to the new one.
5. Execute the script.
This should move about 70+ gb to your new file.
J
>--Original Message--
>If I create another data file is it possible to move data
from one file to
>the other? I currently have a 220Gb and a 50Gb file I
would like to fill up
>the 50Gb file so I can resize the 220Gb file.
Unfortunately I don't have
>much spare storage space so I'm not sure how else I would
do it. Only other
>thing I can think of doing is dropping the entire
database and recreating it
>with more files, then I could restore a backup version.
>Gav
>"Julie" <anonymous@.discussions.microsoft.com> wrote in
message
>news:09fc01c3da8a$b5e91800$a301280a@.phx.gbl...
>> Hello,
>> Uri's solution will work, however you will have to drop
>> and re-create tables to add data to the filegroups.
>> This is because when you create a table (or index) you
>> also state which filegroup you will be saving your data
to.
>> J
>> >--Original Message--
>> >I have a 225Gb database on a test server. It is
currently
>> in one file and I
>> >would like to split it into several files smaller
files.
>> What is the best
>> >way of doing this?
>> >
>> >thanks
>> >
>> >Gav
>> >
>> >
>> >.
>> >
>
>.
>|||Gav
As Julie says, if you change the database and then restore
an old backup you will end up with the structure of the
old database.
If you want to create a second data file (or more) and
populate it you have serveral options.
If you want them poulated evenly, rebuild all your
clustered indexes(or in the case where you don't have any
build a clustered index and the drop it). SQL Server will
spread your tables evenly for you and you should end up
with two near equal data files.
If you want to control size/placement create a second file
group. Make the second data file part of the second file
group. You can then move tables to the new file group from
the design table pane in Enterprise manager.
You could also create a new database and use dts to
transfer the data. You would need more space though to do
that.
Hope this helps
John
>--Original Message--
>Yes, but that sort of depends where your data file is.
for
>instance you can create a data file on a different
drive /
>server than the one your using now, but if you create it
>on a different server you will need to watch your network
>speed.
>The problem is not going to be your data files as such,
>but filling them.
>When you create a table/index you explicitly say
>in T-SQL which data file any data the table is going to
>store saves to.
>Creating extra data files does will not move the data.
>I don't think (and please someone tell me if I'm wrong)
>whether a backup will work as a backup has in it the
>database stucture so if you restore it you also restore
>the number of datafiles you had before i.e 1.
>There is a way of saving some time, and that it to do
with
>indexes.
>About 1 third of all storeage space is used by indexes
>(both clustered and non clusted) so hat you could do is
>BACKUP YOUR DATABASE
>(Sorry bit paraniod about data)
>1. Create a new datafile called indexes.
>2. Under EM Database - all tasks - generate SQL
>Create 2 scripts one to remove all indexes and primary
keys
>one to re create them.
>3. Drop all you indexes
>4. In the script that has the create for the indexes
>perform a search and replace for all the old data file
>name to the new one.
>5. Execute the script.
>This should move about 70+ gb to your new file.
>J
>
>
>>--Original Message--
>>If I create another data file is it possible to move
data
>from one file to
>>the other? I currently have a 220Gb and a 50Gb file I
>would like to fill up
>>the 50Gb file so I can resize the 220Gb file.
>Unfortunately I don't have
>>much spare storage space so I'm not sure how else I
would
>do it. Only other
>>thing I can think of doing is dropping the entire
>database and recreating it
>>with more files, then I could restore a backup version.
>>Gav
>>"Julie" <anonymous@.discussions.microsoft.com> wrote in
>message
>>news:09fc01c3da8a$b5e91800$a301280a@.phx.gbl...
>> Hello,
>> Uri's solution will work, however you will have to drop
>> and re-create tables to add data to the filegroups.
>> This is because when you create a table (or index) you
>> also state which filegroup you will be saving your
data
>to.
>> J
>> >--Original Message--
>> >I have a 225Gb database on a test server. It is
>currently
>> in one file and I
>> >would like to split it into several files smaller
>files.
>> What is the best
>> >way of doing this?
>> >
>> >thanks
>> >
>> >Gav
>> >
>> >
>> >.
>> >
>>
>>.
>.
>