Thursday, February 16, 2012

changing file's filegroup

Hi, I have a database with two data files, both belonging
to the PRIMARY filegroup. I've emptied one of the files
and would like to modify that file's filegroup to
SECONDARY so that I can remove it from my database.
Using the alter database modify filegroup would impact my
entire database and not to a specific file.
Any suggestions. Thanks.Why not just remove it using ALTER DATABASE... REMOVE FILE...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rob" <anonymous@.discussions.microsoft.com> wrote in message news:974b01c48618$64d38f60$a601
280a@.phx.gbl...
> Hi, I have a database with two data files, both belonging
> to the PRIMARY filegroup. I've emptied one of the files
> and would like to modify that file's filegroup to
> SECONDARY so that I can remove it from my database.
> Using the alter database modify filegroup would impact my
> entire database and not to a specific file.
> Any suggestions. Thanks.|||That doesn't work because it already belongs to in the
PRIMARY filegroup and get the following msg when attempted
to do so:
The primary data or log file cannot be removed from a
database.
Thanks.

>--Original Message--
>Why not just remove it using ALTER DATABASE... REMOVE
FILE...
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Rob" <anonymous@.discussions.microsoft.com> wrote in
message news:974b01c48618$64d38f60$a601280a@.phx.gbl...
belonging[vbcol=seagreen]
my[vbcol=seagreen]
>
>.
>|||The filegroup named "PRIMARY" is not the same thing as the primary file (whi
ch generally has .mdf as
extension).
The error message states that you cannot remove the primary file. You can, h
owever remove secondary file
(.ndf) from the primary filegroup (as well as other filegroups).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rob" <anonymous@.discussions.microsoft.com> wrote in message news:926801c48621$4c39ddc0$a501
280a@.phx.gbl...[vbcol=seagreen]
> That doesn't work because it already belongs to in the
> PRIMARY filegroup and get the following msg when attempted
> to do so:
> The primary data or log file cannot be removed from a
> database.
> Thanks.
>
> FILE...
> message news:974b01c48618$64d38f60$a601280a@.phx.gbl...
> belonging
> my|||Both these files have the .mdf extension. I wonder if
detaching the db, renaming the .mdf file in question
to .ndf, re-attaching the db, and then attempt to remove
this file again would work.
Thanks again.

>--Original Message--
>The filegroup named "PRIMARY" is not the same thing as
the primary file (which generally has .mdf as
>extension).
>The error message states that you cannot remove the
primary file. You can, however remove secondary file
>(.ndf) from the primary filegroup (as well as other
filegroups).
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Rob" <anonymous@.discussions.microsoft.com> wrote in
message news:926801c48621$4c39ddc0$a501280a@.phx.gbl...
attempted[vbcol=seagreen]
files[vbcol=seagreen]
impact[vbcol=seagreen]
>
>.
>|||Nope that didn't work... still get the same error message
after I had detached, renamed, and re-attached the db.
Any other thoughts? Thanks.

>--Original Message--
>Both these files have the .mdf extension. I wonder if
>detaching the db, renaming the .mdf file in question
>to .ndf, re-attaching the db, and then attempt to remove
>this file again would work.
>Thanks again.
>
>the primary file (which generally has .mdf as
>primary file. You can, however remove secondary file
>filegroups).
>message news:926801c48621$4c39ddc0$a501280a@.phx.gbl...
>attempted
>files
>impact
>.
>|||You can name the files anything you want, that doesn't affect whether a file
is a primary or secondary
database file. I.e., seems like someone didn't follow the naming conventions
when creating your database.
(There can be only one primary file for each database.)
You can try sp_helpfile. I have a feeling that the one with fileid 1 is the
primary. OTOH, you already know
which is the primary, the one you tried to remove. Empty the other file (the
one which isn't the primary) and
then you can remove that. You cannot remove the primary database file.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rob" <anonymous@.discussions.microsoft.com> wrote in message news:92c001c48624$027652b0$a501
280a@.phx.gbl...[vbcol=seagreen]
> Both these files have the .mdf extension. I wonder if
> detaching the db, renaming the .mdf file in question
> to .ndf, re-attaching the db, and then attempt to remove
> this file again would work.
> Thanks again.
>
> the primary file (which generally has .mdf as
> primary file. You can, however remove secondary file
> filegroups).
> message news:926801c48621$4c39ddc0$a501280a@.phx.gbl...
> attempted
> files
> impact

No comments:

Post a Comment