Backup/restore

We have a large database (30 gigs) that we want to break
up into multiple files. I tried backing it up, and
restoring it to a database created with multiple data
files, but just dumps 30 gigs into the first data file,
and leaves the second one blank.
Any suggestions on breaking up a large database?
Fred...That's not the way to do it. First, use DBCC SHRINKFILE on the larger file.
Next use ALTER DATABASE to increase the size of the same file.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Fred" <fkutz@.surebridge.com> wrote in message
news:4f7201c42c8f$28216840$a601280a@.phx.gbl...
We have a large database (30 gigs) that we want to break
up into multiple files. I tried backing it up, and
restoring it to a database created with multiple data
files, but just dumps 30 gigs into the first data file,
and leaves the second one blank.
Any suggestions on breaking up a large database?
Fred...|||The database is already shrunk.
Why would I increase the single large datafile to make it
larger? I need to break this single large datafile into
multiple parts, so that I can put it on multiple disks.
Fred...
>--Original Message--
>That's not the way to do it. First, use DBCC SHRINKFILE
on the larger file.
>Next use ALTER DATABASE to increase the size of the same
file.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Fred" <fkutz@.surebridge.com> wrote in message
>news:4f7201c42c8f$28216840$a601280a@.phx.gbl...
>We have a large database (30 gigs) that we want to break
>up into multiple files. I tried backing it up, and
>restoring it to a database created with multiple data
>files, but just dumps 30 gigs into the first data file,
>and leaves the second one blank.
>Any suggestions on breaking up a large database?
>Fred...
>.
>|||OK, let's say that You have 2 files - File1 and File2 - both in the same
filegroup. File1 has all of your data and is 10 GB. (Assume File1 is
full.) File2 is also 10 GB and has no data. Run DBCC SHRINKFILE (File1,
5210). What should happen is 5GB moves from File1 to File2, since they are
both in the same filegroup. Now, FIle1 is 5GB and full, while File2 is 10GB
and half-empty. At this point, you can shrink File2 and have two 5GB files
or you can expand File1 to, say, 10 GB and have 2 equally empty files.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
<fkutz@.surebridge.com> wrote in message
news:523e01c42c91$94f65c30$a101280a@.phx.gbl...
The database is already shrunk.
Why would I increase the single large datafile to make it
larger? I need to break this single large datafile into
multiple parts, so that I can put it on multiple disks.
Fred...
>--Original Message--
>That's not the way to do it. First, use DBCC SHRINKFILE
on the larger file.
>Next use ALTER DATABASE to increase the size of the same
file.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Fred" <fkutz@.surebridge.com> wrote in message
>news:4f7201c42c8f$28216840$a601280a@.phx.gbl...
>We have a large database (30 gigs) that we want to break
>up into multiple files. I tried backing it up, and
>restoring it to a database created with multiple data
>files, but just dumps 30 gigs into the first data file,
>and leaves the second one blank.
>Any suggestions on breaking up a large database?
>Fred...
>.
>|||Cool!
Thank you *very* much. That makes much more sense.
Fred...
>--Original Message--
>OK, let's say that You have 2 files - File1 and File2 -
both in the same
>filegroup. File1 has all of your data and is 10 GB.
(Assume File1 is
>full.) File2 is also 10 GB and has no data. Run DBCC
SHRINKFILE (File1,
>5210). What should happen is 5GB moves from File1 to
File2, since they are
>both in the same filegroup. Now, FIle1 is 5GB and full,
while File2 is 10GB
>and half-empty. At this point, you can shrink File2 and
have two 5GB files
>or you can expand File1 to, say, 10 GB and have 2
equally empty files.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
><fkutz@.surebridge.com> wrote in message
>news:523e01c42c91$94f65c30$a101280a@.phx.gbl...
>The database is already shrunk.
>Why would I increase the single large datafile to make it
>larger? I need to break this single large datafile into
>multiple parts, so that I can put it on multiple disks.
>Fred...
>>--Original Message--
>>That's not the way to do it. First, use DBCC SHRINKFILE
>on the larger file.
>>Next use ALTER DATABASE to increase the size of the same
>file.
>>--
>>Tom
>>---
-
>--
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com/sql
>>
>>"Fred" <fkutz@.surebridge.com> wrote in message
>>news:4f7201c42c8f$28216840$a601280a@.phx.gbl...
>>We have a large database (30 gigs) that we want to break
>>up into multiple files. I tried backing it up, and
>>restoring it to a database created with multiple data
>>files, but just dumps 30 gigs into the first data file,
>>and leaves the second one blank.
>>Any suggestions on breaking up a large database?
>>Fred...
>>.
>.
>|||Hey Tom, I tried your suggestion, and it's not working.
Here is the command I'm using and the results:
DBCC SHRINKFILE (EPMROPRD_Data, 15360);
11 1 3925712 128 3925648 3925648
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages,
contact your system administrator.
No data is moving, and the file is not shrinking. My
second file, in the same file group is set to 15360MB.
Am I missing something?
Fred...
>--Original Message--
>OK, let's say that You have 2 files - File1 and File2 -
both in the same
>filegroup. File1 has all of your data and is 10 GB.
(Assume File1 is
>full.) File2 is also 10 GB and has no data. Run DBCC
SHRINKFILE (File1,
>5210). What should happen is 5GB moves from File1 to
File2, since they are
>both in the same filegroup. Now, FIle1 is 5GB and full,
while File2 is 10GB
>and half-empty. At this point, you can shrink File2 and
have two 5GB files
>or you can expand File1 to, say, 10 GB and have 2
equally empty files.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
><fkutz@.surebridge.com> wrote in message
>news:523e01c42c91$94f65c30$a101280a@.phx.gbl...
>The database is already shrunk.
>Why would I increase the single large datafile to make it
>larger? I need to break this single large datafile into
>multiple parts, so that I can put it on multiple disks.
>Fred...
>>--Original Message--
>>That's not the way to do it. First, use DBCC SHRINKFILE
>on the larger file.
>>Next use ALTER DATABASE to increase the size of the same
>file.
>>--
>>Tom
>>---
-
>--
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com/sql
>>
>>"Fred" <fkutz@.surebridge.com> wrote in message
>>news:4f7201c42c8f$28216840$a601280a@.phx.gbl...
>>We have a large database (30 gigs) that we want to break
>>up into multiple files. I tried backing it up, and
>>restoring it to a database created with multiple data
>>files, but just dumps 30 gigs into the first data file,
>>and leaves the second one blank.
>>Any suggestions on breaking up a large database?
>>Fred...
>>.
>.
>|||I'll check into that. Could you please confirm that neither of these files
is the primary file and that the filegroup is not the primary filegroup? If
we're not talking about the primary file/filegroup, then I have an
alternative.
Let's say that you have a filegroup that has the one 30GB file - File1.
Now, add two more files to the filegroup - File1 and File2 - each 15GB in
size. Here's the clincher. Now run:
DBCC SHRINKFILE (File1, EMPTYFILE)
This will force the move and then disallow any further addition of data to
File1. Now run:
ALTER DATABASE MyDB
REMOVE FILE File1
At this point, you can rename File3 to File1:
ALTER DATABASE MyDB
MODIFY FILE (NAME = File3, NEWNAME = File1)
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Fred" <fkutz@.surebridge.com> wrote in message
news:57cf01c42d28$007dc240$a001280a@.phx.gbl...
Hey Tom, I tried your suggestion, and it's not working.
Here is the command I'm using and the results:
DBCC SHRINKFILE (EPMROPRD_Data, 15360);
11 1 3925712 128 3925648 3925648
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages,
contact your system administrator.
No data is moving, and the file is not shrinking. My
second file, in the same file group is set to 15360MB.
Am I missing something?
Fred...
>--Original Message--
>OK, let's say that You have 2 files - File1 and File2 -
both in the same
>filegroup. File1 has all of your data and is 10 GB.
(Assume File1 is
>full.) File2 is also 10 GB and has no data. Run DBCC
SHRINKFILE (File1,
>5210). What should happen is 5GB moves from File1 to
File2, since they are
>both in the same filegroup. Now, FIle1 is 5GB and full,
while File2 is 10GB
>and half-empty. At this point, you can shrink File2 and
have two 5GB files
>or you can expand File1 to, say, 10 GB and have 2
equally empty files.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
><fkutz@.surebridge.com> wrote in message
>news:523e01c42c91$94f65c30$a101280a@.phx.gbl...
>The database is already shrunk.
>Why would I increase the single large datafile to make it
>larger? I need to break this single large datafile into
>multiple parts, so that I can put it on multiple disks.
>Fred...
>>--Original Message--
>>That's not the way to do it. First, use DBCC SHRINKFILE
>on the larger file.
>>Next use ALTER DATABASE to increase the size of the same
>file.
>>--
>>Tom
>>---
-
>--
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com/sql
>>
>>"Fred" <fkutz@.surebridge.com> wrote in message
>>news:4f7201c42c8f$28216840$a601280a@.phx.gbl...
>>We have a large database (30 gigs) that we want to break
>>up into multiple files. I tried backing it up, and
>>restoring it to a database created with multiple data
>>files, but just dumps 30 gigs into the first data file,
>>and leaves the second one blank.
>>Any suggestions on breaking up a large database?
>>Fred...
>>.
>.
>|||Unfortunately they are the primaries. The big one is the
primary file on the primary group. Till now, we've just
been running with a single large file. I had thought of
your alternative as well, but I wasn't sure if it could
be done with the primary file. Though the help files
seem to imply it could. Right now I'm just playing with
a test db, so I can do anything with no worries.
Fred...
>--Original Message--
>I'll check into that. Could you please confirm that
neither of these files
>is the primary file and that the filegroup is not the
primary filegroup? If
>we're not talking about the primary file/filegroup, then
I have an
>alternative.
>Let's say that you have a filegroup that has the one
30GB file - File1.
>Now, add two more files to the filegroup - File1 and
File2 - each 15GB in
>size. Here's the clincher. Now run:
>DBCC SHRINKFILE (File1, EMPTYFILE)
>This will force the move and then disallow any further
addition of data to
>File1. Now run:
>ALTER DATABASE MyDB
>REMOVE FILE File1
>At this point, you can rename File3 to File1:
>ALTER DATABASE MyDB
>MODIFY FILE (NAME = File3, NEWNAME = File1)
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Fred" <fkutz@.surebridge.com> wrote in message
>news:57cf01c42d28$007dc240$a001280a@.phx.gbl...
>Hey Tom, I tried your suggestion, and it's not working.
>Here is the command I'm using and the results:
>DBCC SHRINKFILE (EPMROPRD_Data, 15360);
>11 1 3925712 128 3925648 3925648
>(1 row(s) affected)
>DBCC execution completed. If DBCC printed error messages,
>contact your system administrator.
>No data is moving, and the file is not shrinking. My
>second file, in the same file group is set to 15360MB.
>Am I missing something?
>Fred...
>>--Original Message--
>>OK, let's say that You have 2 files - File1 and File2 -
>both in the same
>>filegroup. File1 has all of your data and is 10 GB.
>(Assume File1 is
>>full.) File2 is also 10 GB and has no data. Run DBCC
>SHRINKFILE (File1,
>>5210). What should happen is 5GB moves from File1 to
>File2, since they are
>>both in the same filegroup. Now, FIle1 is 5GB and full,
>while File2 is 10GB
>>and half-empty. At this point, you can shrink File2 and
>have two 5GB files
>>or you can expand File1 to, say, 10 GB and have 2
>equally empty files.
>>--
>>Tom
>>---
-
>--
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com/sql
>>
>><fkutz@.surebridge.com> wrote in message
>>news:523e01c42c91$94f65c30$a101280a@.phx.gbl...
>>The database is already shrunk.
>>Why would I increase the single large datafile to make
it
>>larger? I need to break this single large datafile into
>>multiple parts, so that I can put it on multiple disks.
>>Fred...
>>--Original Message--
>>That's not the way to do it. First, use DBCC
SHRINKFILE
>>on the larger file.
>>Next use ALTER DATABASE to increase the size of the
same
>>file.
>>--
>>Tom
>>----
-
>-
>>--
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com/sql
>>
>>"Fred" <fkutz@.surebridge.com> wrote in message
>>news:4f7201c42c8f$28216840$a601280a@.phx.gbl...
>>We have a large database (30 gigs) that we want to
break
>>up into multiple files. I tried backing it up, and
>>restoring it to a database created with multiple data
>>files, but just dumps 30 gigs into the first data file,
>>and leaves the second one blank.
>>Any suggestions on breaking up a large database?
>>Fred...
>>.
>>
>>.
>.
>|||It's a good practice to use the primary file and filegroup just for system
objects. What you're now left with is to move data from your primary
filegroup to your application filegroup. This means rebuilding indexes:
CREATE DATABASE Sales
ON PRIMARY
( NAME = SPri1_dat,
FILENAME = 'c:\temp\SPri1dat.mdf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 15% ),
FILEGROUP SalesGroup1
( NAME = SGrp1Fi1_dat,
FILENAME = 'c:\temp\SG1Fi1dt.ndf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 ),
( NAME = SGrp1Fi2_dat,
FILENAME = 'c:\temp\SG1Fi2dt.ndf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = 'Sales_log',
FILENAME = 'c:\temp\salelog.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )
go
use Sales
go
create table t
(
id int constraint PK_t primary key
)
go
insert t
select id
from Northwind..sysobjects
go
create unique clustered index PK_t on t (id) with drop_existing on
SalesGroup1
go
sp_help t
go
use master
go
drop database Sales
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Fred" <fkutz@.surebridge.com> wrote in message
news:578601c42d30$82c6e710$a301280a@.phx.gbl...
Unfortunately they are the primaries. The big one is the
primary file on the primary group. Till now, we've just
been running with a single large file. I had thought of
your alternative as well, but I wasn't sure if it could
be done with the primary file. Though the help files
seem to imply it could. Right now I'm just playing with
a test db, so I can do anything with no worries.
Fred...
>--Original Message--
>I'll check into that. Could you please confirm that
neither of these files
>is the primary file and that the filegroup is not the
primary filegroup? If
>we're not talking about the primary file/filegroup, then
I have an
>alternative.
>Let's say that you have a filegroup that has the one
30GB file - File1.
>Now, add two more files to the filegroup - File1 and
File2 - each 15GB in
>size. Here's the clincher. Now run:
>DBCC SHRINKFILE (File1, EMPTYFILE)
>This will force the move and then disallow any further
addition of data to
>File1. Now run:
>ALTER DATABASE MyDB
>REMOVE FILE File1
>At this point, you can rename File3 to File1:
>ALTER DATABASE MyDB
>MODIFY FILE (NAME = File3, NEWNAME = File1)
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Fred" <fkutz@.surebridge.com> wrote in message
>news:57cf01c42d28$007dc240$a001280a@.phx.gbl...
>Hey Tom, I tried your suggestion, and it's not working.
>Here is the command I'm using and the results:
>DBCC SHRINKFILE (EPMROPRD_Data, 15360);
>11 1 3925712 128 3925648 3925648
>(1 row(s) affected)
>DBCC execution completed. If DBCC printed error messages,
>contact your system administrator.
>No data is moving, and the file is not shrinking. My
>second file, in the same file group is set to 15360MB.
>Am I missing something?
>Fred...
>>--Original Message--
>>OK, let's say that You have 2 files - File1 and File2 -
>both in the same
>>filegroup. File1 has all of your data and is 10 GB.
>(Assume File1 is
>>full.) File2 is also 10 GB and has no data. Run DBCC
>SHRINKFILE (File1,
>>5210). What should happen is 5GB moves from File1 to
>File2, since they are
>>both in the same filegroup. Now, FIle1 is 5GB and full,
>while File2 is 10GB
>>and half-empty. At this point, you can shrink File2 and
>have two 5GB files
>>or you can expand File1 to, say, 10 GB and have 2
>equally empty files.
>>--
>>Tom
>>---
-
>--
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com/sql
>>
>><fkutz@.surebridge.com> wrote in message
>>news:523e01c42c91$94f65c30$a101280a@.phx.gbl...
>>The database is already shrunk.
>>Why would I increase the single large datafile to make
it
>>larger? I need to break this single large datafile into
>>multiple parts, so that I can put it on multiple disks.
>>Fred...
>>--Original Message--
>>That's not the way to do it. First, use DBCC
SHRINKFILE
>>on the larger file.
>>Next use ALTER DATABASE to increase the size of the
same
>>file.
>>--
>>Tom
>>----
-
>-
>>--
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com/sql
>>
>>"Fred" <fkutz@.surebridge.com> wrote in message
>>news:4f7201c42c8f$28216840$a601280a@.phx.gbl...
>>We have a large database (30 gigs) that we want to
break
>>up into multiple files. I tried backing it up, and
>>restoring it to a database created with multiple data
>>files, but just dumps 30 gigs into the first data file,
>>and leaves the second one blank.
>>Any suggestions on breaking up a large database?
>>Fred...
>>.
>>
>>.
>.
>|||In your script, I see nothing indicating a preference for
where your table is built, nor where the data will be
put. Only on the index build do you specify a file
group. How are you proposing I move data between file
groups? (keeping in mind, between tables and views I have
close to 40,000 objects in the database).
>--Original Message--
>It's a good practice to use the primary file and
filegroup just for system
>objects. What you're now left with is to move data from
your primary
>filegroup to your application filegroup. This means
rebuilding indexes:
>CREATE DATABASE Sales
>ON PRIMARY
>( NAME = SPri1_dat,
> FILENAME = 'c:\temp\SPri1dat.mdf',
> SIZE = 10,
> MAXSIZE = 50,
> FILEGROWTH = 15% ),
>FILEGROUP SalesGroup1
>( NAME = SGrp1Fi1_dat,
> FILENAME = 'c:\temp\SG1Fi1dt.ndf',
> SIZE = 10,
> MAXSIZE = 50,
> FILEGROWTH = 5 ),
>( NAME = SGrp1Fi2_dat,
> FILENAME = 'c:\temp\SG1Fi2dt.ndf',
> SIZE = 10,
> MAXSIZE = 50,
> FILEGROWTH = 5 )
>LOG ON
>( NAME = 'Sales_log',
> FILENAME = 'c:\temp\salelog.ldf',
> SIZE = 5MB,
> MAXSIZE = 25MB,
> FILEGROWTH = 5MB )
>go
>use Sales
>go
>create table t
>(
> id int constraint PK_t primary key
>)
>go
>insert t
>select id
>from Northwind..sysobjects
>go
>create unique clustered index PK_t on t (id) with
drop_existing on
>SalesGroup1
>go
>sp_help t
>go
>use master
>go
>drop database Sales
>
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Fred" <fkutz@.surebridge.com> wrote in message
>news:578601c42d30$82c6e710$a301280a@.phx.gbl...
>Unfortunately they are the primaries. The big one is the
>primary file on the primary group. Till now, we've just
>been running with a single large file. I had thought of
>your alternative as well, but I wasn't sure if it could
>be done with the primary file. Though the help files
>seem to imply it could. Right now I'm just playing with
>a test db, so I can do anything with no worries.
>Fred...
>
>>--Original Message--
>>I'll check into that. Could you please confirm that
>neither of these files
>>is the primary file and that the filegroup is not the
>primary filegroup? If
>>we're not talking about the primary file/filegroup, then
>I have an
>>alternative.
>>Let's say that you have a filegroup that has the one
>30GB file - File1.
>>Now, add two more files to the filegroup - File1 and
>File2 - each 15GB in
>>size. Here's the clincher. Now run:
>>DBCC SHRINKFILE (File1, EMPTYFILE)
>>This will force the move and then disallow any further
>addition of data to
>>File1. Now run:
>>ALTER DATABASE MyDB
>>REMOVE FILE File1
>>At this point, you can rename File3 to File1:
>>ALTER DATABASE MyDB
>>MODIFY FILE (NAME = File3, NEWNAME = File1)
>>--
>>Tom
>>---
-
>--
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com/sql
>>
>>"Fred" <fkutz@.surebridge.com> wrote in message
>>news:57cf01c42d28$007dc240$a001280a@.phx.gbl...
>>Hey Tom, I tried your suggestion, and it's not working.
>>Here is the command I'm using and the results:
>>DBCC SHRINKFILE (EPMROPRD_Data, 15360);
>>11 1 3925712 128 3925648
3925648
>>(1 row(s) affected)
>>DBCC execution completed. If DBCC printed error
messages,
>>contact your system administrator.
>>No data is moving, and the file is not shrinking. My
>>second file, in the same file group is set to 15360MB.
>>Am I missing something?
>>Fred...
>>--Original Message--
>>OK, let's say that You have 2 files - File1 and File2 -
>>both in the same
>>filegroup. File1 has all of your data and is 10 GB.
>>(Assume File1 is
>>full.) File2 is also 10 GB and has no data. Run DBCC
>>SHRINKFILE (File1,
>>5210). What should happen is 5GB moves from File1 to
>>File2, since they are
>>both in the same filegroup. Now, FIle1 is 5GB and
full,
>>while File2 is 10GB
>>and half-empty. At this point, you can shrink File2
and
>>have two 5GB files
>>or you can expand File1 to, say, 10 GB and have 2
>>equally empty files.
>>--
>>Tom
>>----
-
>-
>>--
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com/sql
>>
>><fkutz@.surebridge.com> wrote in message
>>news:523e01c42c91$94f65c30$a101280a@.phx.gbl...
>>The database is already shrunk.
>>Why would I increase the single large datafile to make
>it
>>larger? I need to break this single large datafile
into
>>multiple parts, so that I can put it on multiple disks.
>>Fred...
>>--Original Message--
>>That's not the way to do it. First, use DBCC
>SHRINKFILE
>>on the larger file.
>>Next use ALTER DATABASE to increase the size of the
>same
>>file.
>>--
>>Tom
>>---
-
>-
>>-
>>--
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com/sql
>>
>>"Fred" <fkutz@.surebridge.com> wrote in message
>>news:4f7201c42c8f$28216840$a601280a@.phx.gbl...
>>We have a large database (30 gigs) that we want to
>break
>>up into multiple files. I tried backing it up, and
>>restoring it to a database created with multiple data
>>files, but just dumps 30 gigs into the first data
file,
>>and leaves the second one blank.
>>Any suggestions on breaking up a large database?
>>Fred...
>>.
>>
>>.
>>
>>.
>.
>|||When I built the original table t, it was in the default filegroup. Since I
did not explicitly change the default filegroup when I built the DB, it was
the primary filegroup. I implicitly created a clustered index by adding a
primary key to the CREATE TABLE script. If you ran sp_help right after that
table was built, you'd see that the table existed in the primary filegroup.
When I re-created the index, using the drop_existing option, I explicitly
created it on SalesGroup1 by adding "on SalesGroup1". This forces the
clustered index - and hence, the data - to be moved to the new filegroup.
As for doing this on an entire database, you can use EM to script out your
tables, including indexes. Throw away the table stuff and keep the index
scripts. Change all references from the primary filegroup to the new
filegroup. For primary keys, the following will generate your script:
create function CreateIndexScript
(
@.Table sysname
, @.FileGroup sysname
)
returns varchar (8000)
as
begin
declare
@.str varchar (8000)
, @.constraint sysname
, @.IsClustered char (1)
, @.Column sysname
select
@.constraint = CONSTRAINT_NAME
, @.IsClustered = case when indexproperty (object_id (@.Table),
CONSTRAINT_NAME, 'IsClustered') = 1
then 'Y' else 'N' end
from
INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
where
TABLE_NAME = @.Table
and objectproperty (object_id (constraint_name), 'IsPrimaryKey') = 1
select
@.str = 'create unique ' + case when @.IsClustered = 'Y' then 'clustered '
else '' end
+ 'index ' + @.constraint + ' on [' + @.Table + '] ('
declare c cursor fast_forward for
select
COLUMN_NAME
from
information_schema.key_column_usage
where
TABLE_NAME = @.Table
and CONSTRAINT_NAME = @.constraint
order by
ORDINAL_POSITION
open c
fetch c into
@.Column
while @.@.fetch_status = 0
begin
set
@.str = @.str + '[' + @.Column + '], '
fetch c into
@.Column
end
close c
deallocate c
set
@.str = left (@.str, len (@.str) - 1) + ') with drop_existing on [' +
@.FileGroup + ']'
return (@.str)
end
go
select
dbo.CreateIndexScript (TABLE_NAME, 'MyFG')
from
INFORMATION_SCHEMA.TABLES
where
TABLE_TYPE = 'BASE TABLE'
go
drop function CreateIndexScript
You don't need to do anything for your views.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Fred" <fkutz@.surebridge.com> wrote in message
news:59bd01c42d3e$a4603fd0$a101280a@.phx.gbl...
In your script, I see nothing indicating a preference for
where your table is built, nor where the data will be
put. Only on the index build do you specify a file
group. How are you proposing I move data between file
groups? (keeping in mind, between tables and views I have
close to 40,000 objects in the database).
>--Original Message--
>It's a good practice to use the primary file and
filegroup just for system
>objects. What you're now left with is to move data from
your primary
>filegroup to your application filegroup. This means
rebuilding indexes:
>CREATE DATABASE Sales
>ON PRIMARY
>( NAME = SPri1_dat,
> FILENAME = 'c:\temp\SPri1dat.mdf',
> SIZE = 10,
> MAXSIZE = 50,
> FILEGROWTH = 15% ),
>FILEGROUP SalesGroup1
>( NAME = SGrp1Fi1_dat,
> FILENAME = 'c:\temp\SG1Fi1dt.ndf',
> SIZE = 10,
> MAXSIZE = 50,
> FILEGROWTH = 5 ),
>( NAME = SGrp1Fi2_dat,
> FILENAME = 'c:\temp\SG1Fi2dt.ndf',
> SIZE = 10,
> MAXSIZE = 50,
> FILEGROWTH = 5 )
>LOG ON
>( NAME = 'Sales_log',
> FILENAME = 'c:\temp\salelog.ldf',
> SIZE = 5MB,
> MAXSIZE = 25MB,
> FILEGROWTH = 5MB )
>go
>use Sales
>go
>create table t
>(
> id int constraint PK_t primary key
>)
>go
>insert t
>select id
>from Northwind..sysobjects
>go
>create unique clustered index PK_t on t (id) with
drop_existing on
>SalesGroup1
>go
>sp_help t
>go
>use master
>go
>drop database Sales
>
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Fred" <fkutz@.surebridge.com> wrote in message
>news:578601c42d30$82c6e710$a301280a@.phx.gbl...
>Unfortunately they are the primaries. The big one is the
>primary file on the primary group. Till now, we've just
>been running with a single large file. I had thought of
>your alternative as well, but I wasn't sure if it could
>be done with the primary file. Though the help files
>seem to imply it could. Right now I'm just playing with
>a test db, so I can do anything with no worries.
>Fred...
>
>>--Original Message--
>>I'll check into that. Could you please confirm that
>neither of these files
>>is the primary file and that the filegroup is not the
>primary filegroup? If
>>we're not talking about the primary file/filegroup, then
>I have an
>>alternative.
>>Let's say that you have a filegroup that has the one
>30GB file - File1.
>>Now, add two more files to the filegroup - File1 and
>File2 - each 15GB in
>>size. Here's the clincher. Now run:
>>DBCC SHRINKFILE (File1, EMPTYFILE)
>>This will force the move and then disallow any further
>addition of data to
>>File1. Now run:
>>ALTER DATABASE MyDB
>>REMOVE FILE File1
>>At this point, you can rename File3 to File1:
>>ALTER DATABASE MyDB
>>MODIFY FILE (NAME = File3, NEWNAME = File1)
>>--
>>Tom
>>---
-
>--
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com/sql
>>
>>"Fred" <fkutz@.surebridge.com> wrote in message
>>news:57cf01c42d28$007dc240$a001280a@.phx.gbl...
>>Hey Tom, I tried your suggestion, and it's not working.
>>Here is the command I'm using and the results:
>>DBCC SHRINKFILE (EPMROPRD_Data, 15360);
>>11 1 3925712 128 3925648
3925648
>>(1 row(s) affected)
>>DBCC execution completed. If DBCC printed error
messages,
>>contact your system administrator.
>>No data is moving, and the file is not shrinking. My
>>second file, in the same file group is set to 15360MB.
>>Am I missing something?
>>Fred...
>>--Original Message--
>>OK, let's say that You have 2 files - File1 and File2 -
>>both in the same
>>filegroup. File1 has all of your data and is 10 GB.
>>(Assume File1 is
>>full.) File2 is also 10 GB and has no data. Run DBCC
>>SHRINKFILE (File1,
>>5210). What should happen is 5GB moves from File1 to
>>File2, since they are
>>both in the same filegroup. Now, FIle1 is 5GB and
full,
>>while File2 is 10GB
>>and half-empty. At this point, you can shrink File2
and
>>have two 5GB files
>>or you can expand File1 to, say, 10 GB and have 2
>>equally empty files.
>>--
>>Tom
>>----
-
>-
>>--
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com/sql
>>
>><fkutz@.surebridge.com> wrote in message
>>news:523e01c42c91$94f65c30$a101280a@.phx.gbl...
>>The database is already shrunk.
>>Why would I increase the single large datafile to make
>it
>>larger? I need to break this single large datafile
into
>>multiple parts, so that I can put it on multiple disks.
>>Fred...
>>--Original Message--
>>That's not the way to do it. First, use DBCC
>SHRINKFILE
>>on the larger file.
>>Next use ALTER DATABASE to increase the size of the
>same
>>file.
>>--
>>Tom
>>---
-
>-
>>-
>>--
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com/sql
>>
>>"Fred" <fkutz@.surebridge.com> wrote in message
>>news:4f7201c42c8f$28216840$a601280a@.phx.gbl...
>>We have a large database (30 gigs) that we want to
>break
>>up into multiple files. I tried backing it up, and
>>restoring it to a database created with multiple data
>>files, but just dumps 30 gigs into the first data
file,
>>and leaves the second one blank.
>>Any suggestions on breaking up a large database?
>>Fred...
>>.
>>
>>.
>>
>>.
>.
>|||take a look at SQL LiteSpeed for backup and recovery. Thats what I use. Take a look at www.imceda.com