All ndf data files became mdf

We have 1 mdf and 3 ndf datafiles in our database. After a
restore, all ndf files became mdf files. Now we have 4
data files with mdf extension. I believe the ndf and mdf
are the same except that the mdf contains system tables,
but it seems annoying to see all data files with mdf
extension.
Should I detach the database, rename ndf except 1, and re-
attach them? Anybody experience the same problem?
Thanks.It sounds to me that you must be using somme GUI to perform the resore,
right?
Because if you're using the RESTORE command, what you're saying doesn't
quite make sense..
eg, when you restore a database over the pre-existing database using
RESTORE, files aren't created or altered in any way - it's an online
operation that occurs within a "logical" database within the server which is
supported by existing os files. Because the database stays online during the
restore, exclusive locks are held on those files by SQL Server & there's no
way their names could be changed. If you mean that you're restoring the
database to another server, that's another story, but then you actually name
the files in the RESTORE command.
Are you using a GUI to do the restore?
If so, which one - SQL Enterprise Manager or a 3rd party tool?
The answer to your question would depend on which GUI tool you're using to
restore.. Unfortunately for me, I've always found the commands easier /
simpler than the tools (they really are actually) so I'm not too familiar
with the finer points of particular GUI restore tools.. Hopefully someone
else might chip in to help further on that..
Regards,
Greg Linwood
SQL Server MVP
"NoMail" <anonymous@.discussions.microsoft.com> wrote in message
news:d4d001c3ef4b$9b8b01f0$a001280a@.phx.gbl...
> We have 1 mdf and 3 ndf datafiles in our database. After a
> restore, all ndf files became mdf files. Now we have 4
> data files with mdf extension. I believe the ndf and mdf
> are the same except that the mdf contains system tables,
> but it seems annoying to see all data files with mdf
> extension.
> Should I detach the database, rename ndf except 1, and re-
> attach them? Anybody experience the same problem?
> Thanks.|||Hi Greg,
I used Enterprise Manager to restore. I restored to the
same server and database. Another question: How do I know
which data file contains the system tables?
Thanks.........

>--Original Message--
>It sounds to me that you must be using somme GUI to
perform the resore,
>right?
>Because if you're using the RESTORE command, what you're
saying doesn't
>quite make sense..
>eg, when you restore a database over the pre-existing
database using
>RESTORE, files aren't created or altered in any way -
it's an online
>operation that occurs within a "logical" database within
the server which is
>supported by existing os files. Because the database
stays online during the
>restore, exclusive locks are held on those files by SQL
Server & there's no
>way their names could be changed. If you mean that you're
restoring the
>database to another server, that's another story, but
then you actually name
>the files in the RESTORE command.
>Are you using a GUI to do the restore?
>If so, which one - SQL Enterprise Manager or a 3rd party
tool?
>The answer to your question would depend on which GUI
tool you're using to
>restore.. Unfortunately for me, I've always found the
commands easier /
>simpler than the tools (they really are actually) so I'm
not too familiar
>with the finer points of particular GUI restore tools..
Hopefully someone
>else might chip in to help further on that..
>Regards,
>Greg Linwood
>SQL Server MVP
>"NoMail" <anonymous@.discussions.microsoft.com> wrote in
message
>news:d4d001c3ef4b$9b8b01f0$a001280a@.phx.gbl...
After a
re-
>
>.
>|||The system tables are contained in the file(s) that belong to the primary
filegroup.
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"NoMail" <anonymous@.discussions.microsoft.com> wrote in message
news:da2201c3ef59$2041d920$a501280a@.phx.gbl...
> Hi Greg,
> I used Enterprise Manager to restore. I restored to the
> same server and database. Another question: How do I know
> which data file contains the system tables?
> Thanks.........
>
>
> perform the resore,
> saying doesn't
> database using
> it's an online
> the server which is
> stays online during the
> Server & there's no
> restoring the
> then you actually name
> tool?
> tool you're using to
> commands easier /
> not too familiar
> Hopefully someone
> message
> After a
> re-|||Can you clarify please - were you restoring over an existing database or
creating a new database from the backup?
I'd like to test this, so if you could clarify this is would be good..
system objects, including system tables are created in the primary file -
the first file in the primary filegroup. You can get information files in
the primary filegroup by running: exec sp_helpfilegroup 'primary'
Regards,
Greg Linwood
SQL Server MVP
"NoMail" <anonymous@.discussions.microsoft.com> wrote in message
news:da2201c3ef59$2041d920$a501280a@.phx.gbl...
> Hi Greg,
> I used Enterprise Manager to restore. I restored to the
> same server and database. Another question: How do I know
> which data file contains the system tables?
> Thanks.........
>
>
> perform the resore,
> saying doesn't
> database using
> it's an online
> the server which is
> stays online during the
> Server & there's no
> restoring the
> then you actually name
> tool?
> tool you're using to
> commands easier /
> not too familiar
> Hopefully someone
> message
> After a
> re-|||Hi Greg,
Thank you for your help.
I restored to the same existing database, and the same
server - not creating a database from the backup nor
restored to a different server. My environment is W2K, SQL
2K SP3.
I restored the same database before and I had some strange
result in upper case and lower case too, eg. a data file
with name ACCOUNTING.MDF (upper case) to accounting.mdf
(lower case).
Thank you.....................

>--Original Message--
>Can you clarify please - were you restoring over an
existing database or
>creating a new database from the backup?
>I'd like to test this, so if you could clarify this is
would be good..
>system objects, including system tables are created in
the primary file -
>the first file in the primary filegroup. You can get
information files in
>the primary filegroup by running: exec
sp_helpfilegroup 'primary'
>Regards,
>Greg Linwood
>SQL Server MVP
>"NoMail" <anonymous@.discussions.microsoft.com> wrote in
message
>news:da2201c3ef59$2041d920$a501280a@.phx.gbl...
know
you're
within
you're
party
I'm
4
mdf
tables,
and
>
>.
>|||Not quite. A table cannot be limited to a file. System tables are created in
the primary fileGROUP, so if all these files are in the primary filegroup,
the system tables will be spread across all of them.
The only thing special about the primary FILE is that it contains header
information containing names and locations of all the other files in the
database.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:O#30gw17DHA.696@.tk2msftngp13.phx.gbl...
> Can you clarify please - were you restoring over an existing database or
> creating a new database from the backup?
> I'd like to test this, so if you could clarify this is would be good..
> system objects, including system tables are created in the primary file -
> the first file in the primary filegroup. You can get information files in
> the primary filegroup by running: exec sp_helpfilegroup 'primary'
> Regards,
> Greg Linwood
> SQL Server MVP
> "NoMail" <anonymous@.discussions.microsoft.com> wrote in message
> news:da2201c3ef59$2041d920$a501280a@.phx.gbl...
>|||Hi Kalen,
So can I rename all mdf to ndf except the 1st mdf?
(Previously the 1st data file was mdf, all other were ndf)
Thanks...............................

>--Original Message--
>Not quite. A table cannot be limited to a file. System
tables are created in
>the primary fileGROUP, so if all these files are in the
primary filegroup,
>the system tables will be spread across all of them.
>The only thing special about the primary FILE is that it
contains header
>information containing names and locations of all the
other files in the
>database.
>--
>HTH
>--
>Kalen Delaney
>SQL Server MVP
>www.SolidQualityLearning.com
>
>"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
>news:O#30gw17DHA.696@.tk2msftngp13.phx.gbl...
existing database or
would be good..
the primary file -
information files in
sp_helpfilegroup 'primary'
message
the
know
you're
within
SQL
you're
party
I'm
tools..
in
have 4
and mdf
tables,
mdf
and
>
>.
>|||(c: I wasn't 100% sure when adding that last bit to my post - so I got off
my tired butt (up late last night debugging isapi), walked out of my office
over to the bookshelf where all my Inside SQL Servers are lined up in a nice
little row, put down my coffee, opened Inside SQL 2K to chapter 5 (thinking
to myself I'm a sad puppy for knowing the chapter numbers off by heart) &
had a flick for specifics. Nothing obvious jumped up off the page at me, but
it seems I didn't look carefully enough!! (c: but heck, when the author
gives you this kind of personal service, why would you! ;-)
Anyway, I also looked into BOL & found this, which I relied on for the post:
http://msdn.microsoft.com/library/d...r />
_4fht.asp
CREATE DATABASE...Arguments, PRIMARY...
<snip>The first <filespec> entry in the primary filegroup becomes the
primary file, which is the file containing the logical start of the database
and its system tables. A database can have only one primary file. If PRIMARY
is not specified, the first file listed in the CREATE DATABASE statement
becomes the primary file.</snip>
Is BOL wrong or am I just not reading it in proper context?
Regards,
Greg Linwood
SQL Server MVP
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23YTdA717DHA.2056@.TK2MSFTNGP10.phx.gbl...
> Not quite. A table cannot be limited to a file. System tables are created
in
> the primary fileGROUP, so if all these files are in the primary filegroup,
> the system tables will be spread across all of them.
> The only thing special about the primary FILE is that it contains header
> information containing names and locations of all the other files in the
> database.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Greg Linwood" <g_linwoodQhotmail.com> wrote in message
> news:O#30gw17DHA.696@.tk2msftngp13.phx.gbl...
file -
in
>|||Hmm..
I'm not sure what's causing that. I tried creating a database with multiple
files, backing it up, then restoring via SQL EM, both with & without force
restore option & in both cases the .ndf files were still .ndf after the
restore.
I'm on SQL 2000 SP3 as well..
Sorry, but I don't think I can add much more to this, particularly as I'm
not that familiar with SQL EM restore.
Perhaps the only other thing I might suggest is if you could produce a
repro? This would mean you creating a db (preferably with a script, then
recording the exact steps you take with the restore process that lead to the
.ndfs being renamed to .mdf). I do believe that this is happening to you,
but I can't repro it myself unfortunately.
If it helps, here's the create db script I used - it's straight out of SQL
Server Books Online:
USE master
GO
CREATE DATABASE Archive
ON
PRIMARY ( NAME = Arch1,
FILENAME = 'c:\program files\microsoft sql
server\mssql\data\archdat1.mdf',
SIZE = 1MB,
MAXSIZE = 200,
FILEGROWTH = 20),
( NAME = Arch2,
FILENAME = 'c:\program files\microsoft sql
server\mssql\data\archdat2.ndf',
SIZE = 1MB,
MAXSIZE = 200,
FILEGROWTH = 20),
( NAME = Arch3,
FILENAME = 'c:\program files\microsoft sql
server\mssql\data\archdat3.ndf',
SIZE = 1MB,
MAXSIZE = 200,
FILEGROWTH = 20)
LOG ON
( NAME = Archlog1,
FILENAME = 'c:\program files\microsoft sql
server\mssql\data\archlog1.ldf',
SIZE = 1MB,
MAXSIZE = 200,
FILEGROWTH = 20),
( NAME = Archlog2,
FILENAME = 'c:\program files\microsoft sql
server\mssql\data\archlog2.ldf',
SIZE = 1MB,
MAXSIZE = 200,
FILEGROWTH = 20)
GO
use archive
go
create table t1 (c1 int null)
go
insert into t1 values (1)
go
use master
go
backup database archive to disk='c:\archive.fbak'
Regards,
Greg Linwood
SQL Server MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:cd3101c3ef5e$cf5d2c20$a101280a@.phx.gbl...
> Hi Greg,
> Thank you for your help.
> I restored to the same existing database, and the same
> server - not creating a database from the backup nor
> restored to a different server. My environment is W2K, SQL
> 2K SP3.
> I restored the same database before and I had some strange
> result in upper case and lower case too, eg. a data file
> with name ACCOUNTING.MDF (upper case) to accounting.mdf
> (lower case).
> Thank you.....................
>
> existing database or
> would be good..
> the primary file -
> information files in
> sp_helpfilegroup 'primary'
> message
> know
> you're
> within
> you're
> party
> I'm
> 4
> mdf
> tables,
> and