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...
>> 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.
>
>.
>|||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=djq&as_ugroup=microsoft.public.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.........
>
>
> >--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...
> >> 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.
> >
> >
> >.
> >|||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.........
>
>
> >--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...
> >> 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,
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...
>> 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...
>> >> 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.
>> >
>> >
>> >.
>> >
>
>.
>|||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 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...
> > >> 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 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...
>> 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.........
>> >
>> >
>> >
>> >
>> > >--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...
>> > >> 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.
>> > >
>> > >
>> > >.
>> > >
>>
>
>.
>|||(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/default.asp?url=/library/en-us/startsql/getstart_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...
> > 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.........
> > >
> > >
> > >
> > >
> > > >--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...
> > > >> 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.
> > > >
> > > >
> > > >.
> > > >
> >
> >
>|||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.....................
>
> >--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...
> >> 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...
> >> >> 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.
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >|||BOL is wrong in this case.
Maybe next time I'm on a long plane trip, I'll come up with a repro to prove
it, and send it to the BOL folks .
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:u7QA6I27DHA.360@.TK2MSFTNGP12.phx.gbl...
> (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/default.asp?url=/library/en-us/startsql/ge
tstart_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...
> > > 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.........
> > > >
> > > >
> > > >
> > > >
> > > > >--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...
> > > > >> 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.
> > > > >
> > > > >
> > > > >.
> > > > >
> > >
> > >
> >
> >
>|||The suffix is totally arbitrary. You can give the files any names and any
suffix you like. The BOL topic that Greg quoted was correct in this regard:
the first file listed becomes the primary file (no matter you call it.)
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
<anonymous@.discussions.microsoft.com> wrote in message
news:dab101c3ef60$e9426f90$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...
> >> 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.........
> >> >
> >> >
> >> >
> >> >
> >> > >--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...
> >> > >> 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.
> >> > >
> >> > >
> >> > >.
> >> > >
> >>
> >>
> >
> >
> >.
> >|||I'll take your word on it! (c:
Regards,
Greg Linwood
SQL Server MVP
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23rrk5V37DHA.3008@.TK2MSFTNGP09.phx.gbl...
> BOL is wrong in this case.
> Maybe next time I'm on a long plane trip, I'll come up with a repro to
prove
> it, and send it to the BOL folks .
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Greg Linwood" <g_linwoodQhotmail.com> wrote in message
> news:u7QA6I27DHA.360@.TK2MSFTNGP12.phx.gbl...
> > (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/default.asp?url=/library/en-us/startsql/ge
> tstart_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...
> > > > 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.........
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > >--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...
> > > > > >> 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.
> > > > > >
> > > > > >
> > > > > >.
> > > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||this happens to me often because i move db's from prod to test, prod to
dev, etc. it's not a problem. the mdf , ndf, and ldf extensions are
only suggestions.
i reported this problem as a potential bug, but nothing ever came of
it. i guess because the db still works fine with 5 mdf files instead of
1 mdf and 4 ndf files.
NoMail wrote:
> 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.