Logical & Physical files relationships

I have two sql backup files, a and b, of the same database but at different
stages of development.
So, I restore backup a, OK. I then restore backup b, the logical name is the
same as a but the physical name is different.
1. will logical b overwrite logical a?
2. if not, can someone please explain the relationship between sql
server, logical and physical files
Thank you
Frank AshleyI suspect that logical a would disappear when you restore logical b, because
the database is deleted and re-created.
When you create a database, you will have at least 2 physical files. Each of
the files has a logical name. The logical name is used to refer to the files
when using the Alter database modify file commands.(ie Alter database prod
modify file(name=mydatafile, size = 20)
..You may add more physical fles, and delete files as well. At some point in
time your databases was probably dropped and re-created with a different
physical location for the files, using the same logical file names.
Hope this helps.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Frank Ashley" <fashley_NO_SPAM@.clusterseven.com> wrote in message
news:%23rqQASSrEHA.3428@.TK2MSFTNGP11.phx.gbl...
> I have two sql backup files, a and b, of the same database but at
different
> stages of development.
> So, I restore backup a, OK. I then restore backup b, the logical name is
the
> same as a but the physical name is different.
> 1. will logical b overwrite logical a?
> 2. if not, can someone please explain the relationship between sql
> server, logical and physical files
> Thank you
> Frank Ashley
>|||In addition to Wayne's post:
You can have two databases with the same logical database file names. Logical file named need to be
unique *per database* only.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Frank Ashley" <fashley_NO_SPAM@.clusterseven.com> wrote in message
news:%23rqQASSrEHA.3428@.TK2MSFTNGP11.phx.gbl...
>I have two sql backup files, a and b, of the same database but at different
> stages of development.
> So, I restore backup a, OK. I then restore backup b, the logical name is the
> same as a but the physical name is different.
> 1. will logical b overwrite logical a?
> 2. if not, can someone please explain the relationship between sql
> server, logical and physical files
> Thank you
> Frank Ashley
>|||So Wayne's premise that
'I suspect that logical a would disappear when you restore logical b,
because
the database is deleted and re-created.'
is incorrect?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ecIybMUrEHA.2024@.TK2MSFTNGP10.phx.gbl...
> In addition to Wayne's post:
> You can have two databases with the same logical database file names.
Logical file named need to be
> unique *per database* only.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Frank Ashley" <fashley_NO_SPAM@.clusterseven.com> wrote in message
> news:%23rqQASSrEHA.3428@.TK2MSFTNGP11.phx.gbl...
> >I have two sql backup files, a and b, of the same database but at
different
> > stages of development.
> >
> > So, I restore backup a, OK. I then restore backup b, the logical name is
the
> > same as a but the physical name is different.
> >
> > 1. will logical b overwrite logical a?
> > 2. if not, can someone please explain the relationship between sql
> > server, logical and physical files
> >
> > Thank you
> > Frank Ashley
> >
> >
>|||It looks to me like Wayne assumes you are overwriting an existing database
during the restore. In this case, he is correct that the existing database
will be overwritten, including logical file names. Understand that this
will not affect other existing databases because the scope of logical file
names is per-database. You can restore to a different database name and it
can have the same logical file names as existing databases.
>> > 2. if not, can someone please explain the relationship between sql
>> > server, logical and physical files
In summary:
1) a restored database is exactly like the backed-up database except that
the database name and physical file names may be changed during the restore
2) database names must be unique per SQL Server instance
3) logical file names must be unique per database
4) physical file names must be unique per server
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Frank Ashley" <fashley_NO_SPAM@.clusterseven.com> wrote in message
news:OAqSAnVrEHA.756@.TK2MSFTNGP11.phx.gbl...
> So Wayne's premise that
> 'I suspect that logical a would disappear when you restore logical b,
> because
> the database is deleted and re-created.'
> is incorrect?
>
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in
> message news:ecIybMUrEHA.2024@.TK2MSFTNGP10.phx.gbl...
>> In addition to Wayne's post:
>> You can have two databases with the same logical database file names.
> Logical file named need to be
>> unique *per database* only.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Frank Ashley" <fashley_NO_SPAM@.clusterseven.com> wrote in message
>> news:%23rqQASSrEHA.3428@.TK2MSFTNGP11.phx.gbl...
>> >I have two sql backup files, a and b, of the same database but at
> different
>> > stages of development.
>> >
>> > So, I restore backup a, OK. I then restore backup b, the logical name
>> > is
> the
>> > same as a but the physical name is different.
>> >
>> > 1. will logical b overwrite logical a?
>> > 2. if not, can someone please explain the relationship between sql
>> > server, logical and physical files
>> >
>> > Thank you
>> > Frank Ashley
>> >
>> >
>>
>|||Thanks Dan,
That's exactly what I was after (and suspected).
I had always assumed though that SQL Server used its logical names to
'look-up' the physical files, which is why I was confused as to the
'uniqueness' of logical files in a SQl Server instance. Could you shed any
light on the matter for me? How does SQL Server map its instance and logical
and physical files?
Thank you
Frank Ashley
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uvqkfKirEHA.736@.tk2msftngp13.phx.gbl...
> It looks to me like Wayne assumes you are overwriting an existing database
> during the restore. In this case, he is correct that the existing
database
> will be overwritten, including logical file names. Understand that this
> will not affect other existing databases because the scope of logical file
> names is per-database. You can restore to a different database name and
it
> can have the same logical file names as existing databases.
> >> > 2. if not, can someone please explain the relationship between sql
> >> > server, logical and physical files
> In summary:
> 1) a restored database is exactly like the backed-up database except that
> the database name and physical file names may be changed during the
restore
> 2) database names must be unique per SQL Server instance
> 3) logical file names must be unique per database
> 4) physical file names must be unique per server
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Frank Ashley" <fashley_NO_SPAM@.clusterseven.com> wrote in message
> news:OAqSAnVrEHA.756@.TK2MSFTNGP11.phx.gbl...
> > So Wayne's premise that
> >
> > 'I suspect that logical a would disappear when you restore logical b,
> > because
> > the database is deleted and re-created.'
> >
> > is incorrect?
> >
> >
> >
> >
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> > in
> > message news:ecIybMUrEHA.2024@.TK2MSFTNGP10.phx.gbl...
> >> In addition to Wayne's post:
> >>
> >> You can have two databases with the same logical database file names.
> > Logical file named need to be
> >> unique *per database* only.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "Frank Ashley" <fashley_NO_SPAM@.clusterseven.com> wrote in message
> >> news:%23rqQASSrEHA.3428@.TK2MSFTNGP11.phx.gbl...
> >> >I have two sql backup files, a and b, of the same database but at
> > different
> >> > stages of development.
> >> >
> >> > So, I restore backup a, OK. I then restore backup b, the logical name
> >> > is
> > the
> >> > same as a but the physical name is different.
> >> >
> >> > 1. will logical b overwrite logical a?
> >> > 2. if not, can someone please explain the relationship between sql
> >> > server, logical and physical files
> >> >
> >> > Thank you
> >> > Frank Ashley
> >> >
> >> >
> >>
> >>
> >
> >
>|||Logical file names are only used in Transact-SQL statements (e.g. RESTORE,
DBCC, CREATE/ALTER DATABASE, etc.) as user-friendly names to 'look-up'
information in the system tables. These Transact-SQL statements are
database-specific so this is essentially why logical names only need to be
unique within the context of a specific database.
The logical file name and fileid both uniquely identify a file within a
database. This information is stored in the master database sysaltfiles
table along with the database id. The combination of dbid and either
logical name or fileid uniquely identify a file in the sysaltfiles table.
Database file information is also stored in each database in the sysfiles
system table, with a row for each file belonging to that database.
See the Books Online <Books\architec.chm::/8_ar_da2_9sab.htm>.for more
information.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Frank Ashley" <fashley_NO_SPAM@.clusterseven.com> wrote in message
news:%23Gs6XFtrEHA.1232@.TK2MSFTNGP11.phx.gbl...
> Thanks Dan,
> That's exactly what I was after (and suspected).
> I had always assumed though that SQL Server used its logical names to
> 'look-up' the physical files, which is why I was confused as to the
> 'uniqueness' of logical files in a SQl Server instance. Could you shed any
> light on the matter for me? How does SQL Server map its instance and
> logical
> and physical files?
>
> Thank you
> Frank Ashley
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:uvqkfKirEHA.736@.tk2msftngp13.phx.gbl...
>> It looks to me like Wayne assumes you are overwriting an existing
>> database
>> during the restore. In this case, he is correct that the existing
> database
>> will be overwritten, including logical file names. Understand that this
>> will not affect other existing databases because the scope of logical
>> file
>> names is per-database. You can restore to a different database name and
> it
>> can have the same logical file names as existing databases.
>> >> > 2. if not, can someone please explain the relationship between sql
>> >> > server, logical and physical files
>> In summary:
>> 1) a restored database is exactly like the backed-up database except
>> that
>> the database name and physical file names may be changed during the
> restore
>> 2) database names must be unique per SQL Server instance
>> 3) logical file names must be unique per database
>> 4) physical file names must be unique per server
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Frank Ashley" <fashley_NO_SPAM@.clusterseven.com> wrote in message
>> news:OAqSAnVrEHA.756@.TK2MSFTNGP11.phx.gbl...
>> > So Wayne's premise that
>> >
>> > 'I suspect that logical a would disappear when you restore logical
>> > b,
>> > because
>> > the database is deleted and re-created.'
>> >
>> > is incorrect?
>> >
>> >
>> >
>> >
>> >
>> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
>> > wrote
>> > in
>> > message news:ecIybMUrEHA.2024@.TK2MSFTNGP10.phx.gbl...
>> >> In addition to Wayne's post:
>> >>
>> >> You can have two databases with the same logical database file names.
>> > Logical file named need to be
>> >> unique *per database* only.
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >>
>> >>
>> >> "Frank Ashley" <fashley_NO_SPAM@.clusterseven.com> wrote in message
>> >> news:%23rqQASSrEHA.3428@.TK2MSFTNGP11.phx.gbl...
>> >> >I have two sql backup files, a and b, of the same database but at
>> > different
>> >> > stages of development.
>> >> >
>> >> > So, I restore backup a, OK. I then restore backup b, the logical
>> >> > name
>> >> > is
>> > the
>> >> > same as a but the physical name is different.
>> >> >
>> >> > 1. will logical b overwrite logical a?
>> >> > 2. if not, can someone please explain the relationship between
>> >> > sql
>> >> > server, logical and physical files
>> >> >
>> >> > Thank you
>> >> > Frank Ashley
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>