Basic Analysis Server questions

1) Can an AS database be created from multiple data sources .. i.e. can we
create cubes with tables( dimensions) that may belong on different data
sources ?
2) What kind of storage mode do I currently have ? How do I check ? I know
there are a few types such as ROLAP,MOLAP,HOLAP. Is there a way I can find
out from AS Manager how the cube was created ?
3) I believe the default is MOLAP.. And where are the cubes physically
stored ? How can I find the path ?> 1) Can an AS database be created from multiple data sources .. i.e. can we
> create cubes with tables( dimensions) that may belong on different data
> sources ?
With SQL Server 2000, No.
Your best bet is to create views in one data source which reference
structures in the other.
We have a better story coming in SQL Server 2005.

> 2) What kind of storage mode do I currently have ? How do I check ? I know
> there are a few types such as ROLAP,MOLAP,HOLAP. Is there a way I can find
> out from AS Manager how the cube was created ?
If you edit the partition, Analysis Manager tells you storage mode the
partition is in.

> 3) I believe the default is MOLAP.. And where are the cubes physically
> stored ? How can I find the path ?
In SQL Server 2000, everything is stored off the data path.
Typically <install folder>\Data, e.g. C:\Program Files\Microsoft Analysis
Services\Data
Hope that helps.
--
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:ubJ6mBQMFHA.1172@.TK2MSFTNGP12.phx.gbl...
> 1) Can an AS database be created from multiple data sources .. i.e. can we
> create cubes with tables( dimensions) that may belong on different data
> sources ?
> 2) What kind of storage mode do I currently have ? How do I check ? I know
> there are a few types such as ROLAP,MOLAP,HOLAP. Is there a way I can find
> out from AS Manager how the cube was created ?
> 3) I believe the default is MOLAP.. And where are the cubes physically
> stored ? How can I find the path ?
>
>

Basic Analysis Server questions

1) Can an AS database be created from multiple data sources .. i.e. can we
create cubes with tables( dimensions) that may belong on different data
sources ?
2) What kind of storage mode do I currently have ? How do I check ? I know
there are a few types such as ROLAP,MOLAP,HOLAP. Is there a way I can find
out from AS Manager how the cube was created ?
3) I believe the default is MOLAP.. And where are the cubes physically
stored ? How can I find the path ?
> 1) Can an AS database be created from multiple data sources .. i.e. can we
> create cubes with tables( dimensions) that may belong on different data
> sources ?
With SQL Server 2000, No.
Your best bet is to create views in one data source which reference
structures in the other.
We have a better story coming in SQL Server 2005.

> 2) What kind of storage mode do I currently have ? How do I check ? I know
> there are a few types such as ROLAP,MOLAP,HOLAP. Is there a way I can find
> out from AS Manager how the cube was created ?
If you edit the partition, Analysis Manager tells you storage mode the
partition is in.

> 3) I believe the default is MOLAP.. And where are the cubes physically
> stored ? How can I find the path ?
In SQL Server 2000, everything is stored off the data path.
Typically <install folder>\Data, e.g. C:\Program Files\Microsoft Analysis
Services\Data
Hope that helps.
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:ubJ6mBQMFHA.1172@.TK2MSFTNGP12.phx.gbl...
> 1) Can an AS database be created from multiple data sources .. i.e. can we
> create cubes with tables( dimensions) that may belong on different data
> sources ?
> 2) What kind of storage mode do I currently have ? How do I check ? I know
> there are a few types such as ROLAP,MOLAP,HOLAP. Is there a way I can find
> out from AS Manager how the cube was created ?
> 3) I believe the default is MOLAP.. And where are the cubes physically
> stored ? How can I find the path ?
>
>

Backuping up multiple databases at once

I'm going through the docs and I can't find a way to backup multiple databases at once, rather than them individually. I see you can create a backup device, but you have to select every individual database. I know in 2000 you could do it ....

Are you referring to using a maintanence plan or t-sql? I assume a maintanence plan?

Regardless, you can't backup multiple db's in a single backup operation...what you can do is create multiple backup statements, one for each database (which is what would happen in SQL 2000 if you chose multiple db's from the GUI), then each database will be backed up serially (i.e. one after the other), and would look like this in t-sql:

backup db1 to disk....

backup db2 to disk....

backup db3 to disk....

If you'd like to backup multiple db's in parallel, you could take each of your t-sql backup commands and run them across seperate connections, or create seperate scheduled jobs for each...

I don't have the 2005 GUI on this computer, so I can't validate/verify the selection of multiple db's from it, but I would be surprised if you can't do it from the Maintanence Plan...seems odd...I'll check later and get back to you if nobody else responds before I get to it...

HTH,

|||

Thanks Chad

That works out just fine.. i can do it individually . I thought I was missing an obvious setting or something . I'm not that experienced with 2005 and I wasn't sure.

I appreciate the response.

|||

Backup task UI of Maintenace plan provides selection of multiple databases.

Gops Dwarak

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
>----
--[vbcol=seagreen]
>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...
>on the larger file.
>file.
-
>--
>.
>
|||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
>----
--[vbcol=seagreen]
>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...
>on the larger file.
>file.
-
>--
>.
>
|||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
>----
--[vbcol=seagreen]
>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...
>on the larger file.
>file.
-
>--
>.
>
|||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
>----
--[vbcol=seagreen]
>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...
>both in the same
>(Assume File1 is
>SHRINKFILE (File1,
>File2, since they are
>while File2 is 10GB
>have two 5GB files
>equally empty files.
-[vbcol=seagreen]
>--
it[vbcol=seagreen]
SHRINKFILE[vbcol=seagreen]
same[vbcol=seagreen]
-[vbcol=seagreen]
>-
break
>.
>
|||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
>----
--[vbcol=seagreen]
>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...
>
>neither of these files
>primary filegroup? If
>I have an
>30GB file - File1.
>File2 - each 15GB in
>addition of data to
-[vbcol=seagreen]
>--
3925648[vbcol=seagreen]
messages,[vbcol=seagreen]
full,[vbcol=seagreen]
and[vbcol=seagreen]
-[vbcol=seagreen]
>-
>it
into[vbcol=seagreen]
>SHRINKFILE
>same
-[vbcol=seagreen]
>-
>break
file,
>.
>
|||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
>----
--[vbcol=seagreen]
>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...
>
>neither of these files
>primary filegroup? If
>I have an
>30GB file - File1.
>File2 - each 15GB in
>addition of data to
-[vbcol=seagreen]
>--
3925648[vbcol=seagreen]
messages,[vbcol=seagreen]
full,[vbcol=seagreen]
and[vbcol=seagreen]
-[vbcol=seagreen]
>-
>it
into[vbcol=seagreen]
>SHRINKFILE
>same
-[vbcol=seagreen]
>-
>break
file,
>.
>

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

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...
>
>on the larger file.
>file.
-[vbcol=seagreen]
>--
>.
>|||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...
>
>on the larger file.
>file.
-[vbcol=seagreen]
>--
>.
>|||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...
>
>on the larger file.
>file.
-[vbcol=seagreen]
>--
>.
>|||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...
>
>both in the same
>(Assume File1 is
>SHRINKFILE (File1,
>File2, since they are
>while File2 is 10GB
>have two 5GB files
>equally empty files.
-[vbcol=seagreen]
>--
it[vbcol=seagreen]
SHRINKFILE[vbcol=seagreen]
same[vbcol=seagreen]
-[vbcol=seagreen]
>-
break[vbcol=seagreen]
>.
>|||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...
>
>neither of these files
>primary filegroup? If
>I have an
>30GB file - File1.
>File2 - each 15GB in
>addition of data to
-[vbcol=seagreen]
>--
3925648[vbcol=seagreen]
messages,[vbcol=seagreen]
full,[vbcol=seagreen]
and[vbcol=seagreen]
-[vbcol=seagreen]
>-
>it
into[vbcol=seagreen]
>SHRINKFILE
>same
-[vbcol=seagreen]
>-
>break
file,[vbcol=seagreen]
>.
>|||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...
>
>neither of these files
>primary filegroup? If
>I have an
>30GB file - File1.
>File2 - each 15GB in
>addition of data to
-[vbcol=seagreen]
>--
3925648[vbcol=seagreen]
messages,[vbcol=seagreen]
full,[vbcol=seagreen]
and[vbcol=seagreen]
-[vbcol=seagreen]
>-
>it
into[vbcol=seagreen]
>SHRINKFILE
>same
-[vbcol=seagreen]
>-
>break
file,[vbcol=seagreen]
>.
>

backup, restore commands for db with multiple data files and fileg

I use the below code to backup the database with multiple datafiles, and the
n
try to restore with the command below, but it throws error.
How do I perform backup/restore of database with multiple data files and log
files?
BACKUP DATABASE abc
FILE = 'abc_Data', FILEGROUP = 'PRIMARY',
FILE = 'abc_DataLOB', FILEGROUP = 'FG_abc_DataLOB',
FILE = 'abc_dat', FILEGROUP = 'FG_abc_dat',
FILE = 'abc_dat2', FILEGROUP = 'FG_abcdat2',
FILE = 'abc_dat3', FILEGROUP = 'FG_abcdat3',
TO DISK = 'd:\SQLBackups\abc_200507121535.BAK'
WITH INIT, STATS = 10
go
RESTORE DATABASE abc
FROM DISK ='d:\SQLBackups\abc_200507121535.BAK'
WITH
MOVE 'abc_Data' TO 'd:\MSSQL\MSSQL\data\abc_Data.MDF',
MOVE 'abc_DataLOB' TO
'd:\MSSQL\MSSQL\data\abc_DataLOB.NDF',
MOVE 'abc_dat' TO 'd:\MSSQL\MSSQL\data\abc_dat.NDF' ,
MOVE 'abc_dat2' TO
'd:\MSSQL\MSSQL\data\abc_dat2.NDF' ,
MOVE 'abc_dat3' TO 'd:\MSSQL\MSSQL\data\abc_dat3.NDF',
MOVE 'abc_Log1' TO
'D:\MSSQL\MSSQL\Data\abc_Log1.LDF',
MOVE 'abc_Log2' TO 'D:\MSSQL\MSSQL\Data\abc_Log2.LDF',
RECOVERY, STATS = 10
GOBackup the entire db instead of backing up specific files or filegroups.
BACKUP DATABASE abc
TO DISK = 'd:\SQLBackups\abc_200507121535.BAK'
WITH INIT, STATS = 10
go
AMB
"Pari" wrote:

> I use the below code to backup the database with multiple datafiles, and t
hen
> try to restore with the command below, but it throws error.
> How do I perform backup/restore of database with multiple data files and l
og
> files?
> BACKUP DATABASE abc
> FILE = 'abc_Data', FILEGROUP = 'PRIMARY',
> FILE = 'abc_DataLOB', FILEGROUP = 'FG_abc_DataLOB',
> FILE = 'abc_dat', FILEGROUP = 'FG_abc_dat',
> FILE = 'abc_dat2', FILEGROUP = 'FG_abcdat2',
> FILE = 'abc_dat3', FILEGROUP = 'FG_abcdat3',
> TO DISK = 'd:\SQLBackups\abc_200507121535.BAK'
> WITH INIT, STATS = 10
> go
> RESTORE DATABASE abc
> FROM DISK ='d:\SQLBackups\abc_200507121535.BAK'
> WITH
> MOVE 'abc_Data' TO 'd:\MSSQL\MSSQL\data\abc_Data.MDF',
> MOVE 'abc_DataLOB' TO
> 'd:\MSSQL\MSSQL\data\abc_DataLOB.NDF',
> MOVE 'abc_dat' TO 'd:\MSSQL\MSSQL\data\abc_dat.NDF' ,
> MOVE 'abc_dat2' TO
> 'd:\MSSQL\MSSQL\data\abc_dat2.NDF' ,
> MOVE 'abc_dat3' TO 'd:\MSSQL\MSSQL\data\abc_dat3.NDF',
> MOVE 'abc_Log1' TO
> 'D:\MSSQL\MSSQL\Data\abc_Log1.LDF',
> MOVE 'abc_Log2' TO 'D:\MSSQL\MSSQL\Data\abc_Log2.LDF',
> RECOVERY, STATS = 10
> GO
>

backup, restore commands for db with multiple data files and fileg

I use the below code to backup the database with multiple datafiles, and then
try to restore with the command below, but it throws error.
How do I perform backup/restore of database with multiple data files and log
files?
BACKUP DATABASE abc
FILE = 'abc_Data', FILEGROUP = 'PRIMARY',
FILE = 'abc_DataLOB', FILEGROUP = 'FG_abc_DataLOB',
FILE = 'abc_dat', FILEGROUP = 'FG_abc_dat',
FILE = 'abc_dat2', FILEGROUP = 'FG_abcdat2',
FILE = 'abc_dat3', FILEGROUP = 'FG_abcdat3',
TO DISK = 'd:\SQLBackups\abc_200507121535.BAK'
WITH INIT, STATS = 10
go
RESTORE DATABASE abc
FROM DISK ='d:\SQLBackups\abc_200507121535.BAK'
WITH
MOVE 'abc_Data' TO 'd:\MSSQL\MSSQL\data\abc_Data.MDF',
MOVE 'abc_DataLOB' TO
'd:\MSSQL\MSSQL\data\abc_DataLOB.NDF',
MOVE 'abc_dat' TO 'd:\MSSQL\MSSQL\data\abc_dat.NDF' ,
MOVE 'abc_dat2' TO
'd:\MSSQL\MSSQL\data\abc_dat2.NDF' ,
MOVE 'abc_dat3' TO 'd:\MSSQL\MSSQL\data\abc_dat3.NDF',
MOVE 'abc_Log1' TO
'D:\MSSQL\MSSQL\Data\abc_Log1.LDF',
MOVE 'abc_Log2' TO 'D:\MSSQL\MSSQL\Data\abc_Log2.LDF',
RECOVERY, STATS = 10
GOBackup the entire db instead of backing up specific files or filegroups.
BACKUP DATABASE abc
TO DISK = 'd:\SQLBackups\abc_200507121535.BAK'
WITH INIT, STATS = 10
go
AMB
"Pari" wrote:
> I use the below code to backup the database with multiple datafiles, and then
> try to restore with the command below, but it throws error.
> How do I perform backup/restore of database with multiple data files and log
> files?
> BACKUP DATABASE abc
> FILE = 'abc_Data', FILEGROUP = 'PRIMARY',
> FILE = 'abc_DataLOB', FILEGROUP = 'FG_abc_DataLOB',
> FILE = 'abc_dat', FILEGROUP = 'FG_abc_dat',
> FILE = 'abc_dat2', FILEGROUP = 'FG_abcdat2',
> FILE = 'abc_dat3', FILEGROUP = 'FG_abcdat3',
> TO DISK = 'd:\SQLBackups\abc_200507121535.BAK'
> WITH INIT, STATS = 10
> go
> RESTORE DATABASE abc
> FROM DISK ='d:\SQLBackups\abc_200507121535.BAK'
> WITH
> MOVE 'abc_Data' TO 'd:\MSSQL\MSSQL\data\abc_Data.MDF',
> MOVE 'abc_DataLOB' TO
> 'd:\MSSQL\MSSQL\data\abc_DataLOB.NDF',
> MOVE 'abc_dat' TO 'd:\MSSQL\MSSQL\data\abc_dat.NDF' ,
> MOVE 'abc_dat2' TO
> 'd:\MSSQL\MSSQL\data\abc_dat2.NDF' ,
> MOVE 'abc_dat3' TO 'd:\MSSQL\MSSQL\data\abc_dat3.NDF',
> MOVE 'abc_Log1' TO
> 'D:\MSSQL\MSSQL\Data\abc_Log1.LDF',
> MOVE 'abc_Log2' TO 'D:\MSSQL\MSSQL\Data\abc_Log2.LDF',
> RECOVERY, STATS = 10
> GO
>

backup, restore commands for db with multiple data files and fileg

I use the below code to backup the database with multiple datafiles, and then
try to restore with the command below, but it throws error.
How do I perform backup/restore of database with multiple data files and log
files?
BACKUP DATABASE abc
FILE = 'abc_Data', FILEGROUP = 'PRIMARY',
FILE = 'abc_DataLOB', FILEGROUP = 'FG_abc_DataLOB',
FILE = 'abc_dat', FILEGROUP = 'FG_abc_dat',
FILE = 'abc_dat2', FILEGROUP = 'FG_abcdat2',
FILE = 'abc_dat3', FILEGROUP = 'FG_abcdat3',
TO DISK = 'd:\SQLBackups\abc_200507121535.BAK'
WITH INIT, STATS = 10
go
RESTORE DATABASE abc
FROM DISK ='d:\SQLBackups\abc_200507121535.BAK'
WITH
MOVE 'abc_Data' TO 'd:\MSSQL\MSSQL\data\abc_Data.MDF',
MOVE 'abc_DataLOB' TO
'd:\MSSQL\MSSQL\data\abc_DataLOB.NDF',
MOVE 'abc_dat' TO 'd:\MSSQL\MSSQL\data\abc_dat.NDF' ,
MOVE 'abc_dat2' TO
'd:\MSSQL\MSSQL\data\abc_dat2.NDF' ,
MOVE 'abc_dat3' TO 'd:\MSSQL\MSSQL\data\abc_dat3.NDF',
MOVE 'abc_Log1' TO
'D:\MSSQL\MSSQL\Data\abc_Log1.LDF',
MOVE 'abc_Log2' TO 'D:\MSSQL\MSSQL\Data\abc_Log2.LDF',
RECOVERY, STATS = 10
GO
Backup the entire db instead of backing up specific files or filegroups.
BACKUP DATABASE abc
TO DISK = 'd:\SQLBackups\abc_200507121535.BAK'
WITH INIT, STATS = 10
go
AMB
"Pari" wrote:

> I use the below code to backup the database with multiple datafiles, and then
> try to restore with the command below, but it throws error.
> How do I perform backup/restore of database with multiple data files and log
> files?
> BACKUP DATABASE abc
> FILE = 'abc_Data', FILEGROUP = 'PRIMARY',
> FILE = 'abc_DataLOB', FILEGROUP = 'FG_abc_DataLOB',
> FILE = 'abc_dat', FILEGROUP = 'FG_abc_dat',
> FILE = 'abc_dat2', FILEGROUP = 'FG_abcdat2',
> FILE = 'abc_dat3', FILEGROUP = 'FG_abcdat3',
> TO DISK = 'd:\SQLBackups\abc_200507121535.BAK'
> WITH INIT, STATS = 10
> go
> RESTORE DATABASE abc
> FROM DISK ='d:\SQLBackups\abc_200507121535.BAK'
> WITH
> MOVE 'abc_Data' TO 'd:\MSSQL\MSSQL\data\abc_Data.MDF',
> MOVE 'abc_DataLOB' TO
> 'd:\MSSQL\MSSQL\data\abc_DataLOB.NDF',
> MOVE 'abc_dat' TO 'd:\MSSQL\MSSQL\data\abc_dat.NDF' ,
> MOVE 'abc_dat2' TO
> 'd:\MSSQL\MSSQL\data\abc_dat2.NDF' ,
> MOVE 'abc_dat3' TO 'd:\MSSQL\MSSQL\data\abc_dat3.NDF',
> MOVE 'abc_Log1' TO
> 'D:\MSSQL\MSSQL\Data\abc_Log1.LDF',
> MOVE 'abc_Log2' TO 'D:\MSSQL\MSSQL\Data\abc_Log2.LDF',
> RECOVERY, STATS = 10
> GO
>