Logging query errors: backup

Showing posts with label backup. Show all posts

Monday, March 19, 2012

Login failed Error

Hello,
I am using Sql Server 2000 and am having problems after I tried restoring my
db today.
I had got the database backup file from another user and I was able to
restore my db successfully. But after doing so I was not able to query any
of my tables.
Any query I give like,
select * from tablename yielded Invalid object name 'tablename'
Earlier the owner of this db was not "sa" but rather "celia".
After restoring the db, I am not able to login to SQL Server Query Analyser
using celia/password.
I logged in as "sa" then and had to execute my query like
select * from celia.tablename and it worked this time.
Any idea on what went wrong? How do I get this back to working?
Thanks.
CeliaHi,
You can syncronze the login and users first. This will allow you to login
using celia user. Use the below procedure to sync the Login and user
sp_change_users_login 'update_one','celia','celia' -- (See books online for
more information)
Any idea on what went wrong? How do I get this back to working?
Since the object owner (table) is CELIA, even if you login as SA, you need
to mention table owner name (CELIA.Table_name).
This prblem will be solved once you sync. the login and user.
Incase if you want to use SA or other users to access the table with out
table owner qualifier than chnage the object owner to DBO.
This can be done using the system procedure 'sp_changeobjectowner' (See
books online for more info)
Thanks
Hari
SQL Server MVP
"msnews" <jukliukiuk@.celia.com> wrote in message
news:%23Fez80wzFHA.1968@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I am using Sql Server 2000 and am having problems after I tried restoring
> my
> db today.
> I had got the database backup file from another user and I was able to
> restore my db successfully. But after doing so I was not able to query any
> of my tables.
> Any query I give like,
> select * from tablename yielded Invalid object name 'tablename'
> Earlier the owner of this db was not "sa" but rather "celia".
> After restoring the db, I am not able to login to SQL Server Query
> Analyser
> using celia/password.
> I logged in as "sa" then and had to execute my query like
> select * from celia.tablename and it worked this time.
> Any idea on what went wrong? How do I get this back to working?
> Thanks.
> Celia
>
>

Login failed Error

Hello,
I am using Sql Server 2000 and am having problems after I tried restoring my
db today.
I had got the database backup file from another user and I was able to
restore my db successfully. But after doing so I was not able to query any
of my tables.
Any query I give like,
select * from tablename yielded Invalid object name 'tablename'
Earlier the owner of this db was not "sa" but rather "celia".
After restoring the db, I am not able to login to SQL Server Query Analyser
using celia/password.
I logged in as "sa" then and had to execute my query like
select * from celia.tablename and it worked this time.
Any idea on what went wrong? How do I get this back to working?
Thanks.
Celia
Hi,
You can syncronze the login and users first. This will allow you to login
using celia user. Use the below procedure to sync the Login and user
sp_change_users_login 'update_one','celia','celia' -- (See books online for
more information)
Any idea on what went wrong? How do I get this back to working?
Since the object owner (table) is CELIA, even if you login as SA, you need
to mention table owner name (CELIA.Table_name).
This prblem will be solved once you sync. the login and user.
Incase if you want to use SA or other users to access the table with out
table owner qualifier than chnage the object owner to DBO.
This can be done using the system procedure 'sp_changeobjectowner' (See
books online for more info)
Thanks
Hari
SQL Server MVP
"msnews" <jukliukiuk@.celia.com> wrote in message
news:%23Fez80wzFHA.1968@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I am using Sql Server 2000 and am having problems after I tried restoring
> my
> db today.
> I had got the database backup file from another user and I was able to
> restore my db successfully. But after doing so I was not able to query any
> of my tables.
> Any query I give like,
> select * from tablename yielded Invalid object name 'tablename'
> Earlier the owner of this db was not "sa" but rather "celia".
> After restoring the db, I am not able to login to SQL Server Query
> Analyser
> using celia/password.
> I logged in as "sa" then and had to execute my query like
> select * from celia.tablename and it worked this time.
> Any idea on what went wrong? How do I get this back to working?
> Thanks.
> Celia
>
>

Friday, March 9, 2012

Login account in two servers

Dear All,
The background is: I create a set of login account in the
production SQL 2K server (namely A) and assign some roles
to them. I usually backup the database and restore it to
our backup SQL 2K server (namely B) daily.
I would like to create the same set of login acount in B
server but it usally prompt me that there is already an
account in the related database. I believe this is
because I restore the whole database from A to B. In SQL
6.5, I will do a trick to update the uid/sid so that both
server of those login account are the same but in SQL 2K,
it dosen't allow me to do so.
Any idea?
Thanks.
Hi,
Looks like the Login and user chan has lost. To bring up the chain you could
use the stored procedure "sp_change_users_login". Have a look in SQL server
books online for the usage of the stored procedure.
Thanks
Hari
MCDBA
"Charles Lee" <leech@.dnb.com> wrote in message
news:49eb01c47389$30889df0$a401280a@.phx.gbl...
> Dear All,
> The background is: I create a set of login account in the
> production SQL 2K server (namely A) and assign some roles
> to them. I usually backup the database and restore it to
> our backup SQL 2K server (namely B) daily.
> I would like to create the same set of login acount in B
> server but it usally prompt me that there is already an
> account in the related database. I believe this is
> because I restore the whole database from A to B. In SQL
> 6.5, I will do a trick to update the uid/sid so that both
> server of those login account are the same but in SQL 2K,
> it dosen't allow me to do so.
> Any idea?
> Thanks.
|||In books on line, look up "log shipping". While changing the roles for log
shipping, one must ensure that the new server has all of the logins, etc
from the old server. There is really nice documentation about all of the
steps necessary to do this.. ( including the use of sp_change_users_login)
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
"Charles Lee" <leech@.dnb.com> wrote in message
news:49eb01c47389$30889df0$a401280a@.phx.gbl...
> Dear All,
> The background is: I create a set of login account in the
> production SQL 2K server (namely A) and assign some roles
> to them. I usually backup the database and restore it to
> our backup SQL 2K server (namely B) daily.
> I would like to create the same set of login acount in B
> server but it usally prompt me that there is already an
> account in the related database. I believe this is
> because I restore the whole database from A to B. In SQL
> 6.5, I will do a trick to update the uid/sid so that both
> server of those login account are the same but in SQL 2K,
> it dosen't allow me to do so.
> Any idea?
> Thanks.

Login account in two servers

Dear All,
The background is: I create a set of login account in the
production SQL 2K server (namely A) and assign some roles
to them. I usually backup the database and restore it to
our backup SQL 2K server (namely B) daily.
I would like to create the same set of login acount in B
server but it usally prompt me that there is already an
account in the related database. I believe this is
because I restore the whole database from A to B. In SQL
6.5, I will do a trick to update the uid/sid so that both
server of those login account are the same but in SQL 2K,
it dosen't allow me to do so.
Any idea?
Thanks.Hi,
Looks like the Login and user chan has lost. To bring up the chain you could
use the stored procedure "sp_change_users_login". Have a look in SQL server
books online for the usage of the stored procedure.
Thanks
Hari
MCDBA
"Charles Lee" <leech@.dnb.com> wrote in message
news:49eb01c47389$30889df0$a401280a@.phx.gbl...
> Dear All,
> The background is: I create a set of login account in the
> production SQL 2K server (namely A) and assign some roles
> to them. I usually backup the database and restore it to
> our backup SQL 2K server (namely B) daily.
> I would like to create the same set of login acount in B
> server but it usally prompt me that there is already an
> account in the related database. I believe this is
> because I restore the whole database from A to B. In SQL
> 6.5, I will do a trick to update the uid/sid so that both
> server of those login account are the same but in SQL 2K,
> it dosen't allow me to do so.
> Any idea?
> Thanks.|||In books on line, look up "log shipping". While changing the roles for log
shipping, one must ensure that the new server has all of the logins, etc
from the old server. There is really nice documentation about all of the
steps necessary to do this.. ( including the use of sp_change_users_login)
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
"Charles Lee" <leech@.dnb.com> wrote in message
news:49eb01c47389$30889df0$a401280a@.phx.gbl...
> Dear All,
> The background is: I create a set of login account in the
> production SQL 2K server (namely A) and assign some roles
> to them. I usually backup the database and restore it to
> our backup SQL 2K server (namely B) daily.
> I would like to create the same set of login acount in B
> server but it usally prompt me that there is already an
> account in the related database. I believe this is
> because I restore the whole database from A to B. In SQL
> 6.5, I will do a trick to update the uid/sid so that both
> server of those login account are the same but in SQL 2K,
> it dosen't allow me to do so.
> Any idea?
> Thanks.

Wednesday, March 7, 2012

logical device already exists?

I want to back the master db to the disk file e:\data\MSSQL\BACKUP\master.BAK.
When I tried to create a new backup device called 'master', I got the error:
Error 15026: Logical device 'master' already exists.
But I've checked several times and don't see master.BAK already exist in e:\data\MSSQL\BACKUP. I had no problem creating the backup devices for other system default databases, like msdb, model, etc.
Anybody know what might be the problem?
Bing
When you create a backup device you are associating a logical name to a
physical file name. In SQL Server there is more than one type of device.
If you look in BOL under sysdevices you can find out about all the different
devices. Anyway "master" already is a device that is associated with the
actual master database. If you want to create a logical backup device to
refer to your master database backup, call it "master_bak", or something.
Hope this helps you understand what a device is. If you really want to see
all the devices you already have assigned you can run the following command
from QA:
select * from master.dbo.sysdevices
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"bing" <bing@.discussions.microsoft.com> wrote in message
news:9AC7B842-64A8-4790-8C39-87957075EE39@.microsoft.com...
> I want to back the master db to the disk file
e:\data\MSSQL\BACKUP\master.BAK.
> When I tried to create a new backup device called 'master', I got the
error:
> Error 15026: Logical device 'master' already exists.
> But I've checked several times and don't see master.BAK already exist in
e:\data\MSSQL\BACKUP. I had no problem creating the backup devices for
other system default databases, like msdb, model, etc.
> Anybody know what might be the problem?
> Bing
|||Hi,
It seems there is an entry in master..sysdevices table.
Execute the below procedure from Query ANalyzer and ensure that you do not
have the same file.
sp_helpdevice
To double check the same by querying :-
select * from master..sysdevices
If you have an entry then you can drop the device by using the below command
(Execute the comand if you need)
sp_dropdevice 'device_name'
Thanks
Hari
MCDBA
Thanks
Hari
MCDBA
"bing" <bing@.discussions.microsoft.com> wrote in message
news:9AC7B842-64A8-4790-8C39-87957075EE39@.microsoft.com...
> I want to back the master db to the disk file
e:\data\MSSQL\BACKUP\master.BAK.
> When I tried to create a new backup device called 'master', I got the
error:
> Error 15026: Logical device 'master' already exists.
> But I've checked several times and don't see master.BAK already exist in
e:\data\MSSQL\BACKUP. I had no problem creating the backup devices for
other system default databases, like msdb, model, etc.
> Anybody know what might be the problem?
> Bing
|||Thanks all for the information. Very helpful.
Bing
"Hari Prasad" wrote:

> Hi,
> It seems there is an entry in master..sysdevices table.
> Execute the below procedure from Query ANalyzer and ensure that you do not
> have the same file.
> sp_helpdevice
> To double check the same by querying :-
> select * from master..sysdevices
> If you have an entry then you can drop the device by using the below command
> (Execute the comand if you need)
> sp_dropdevice 'device_name'
> Thanks
> Hari
> MCDBA
>
> Thanks
> Hari
> MCDBA
>
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:9AC7B842-64A8-4790-8C39-87957075EE39@.microsoft.com...
> e:\data\MSSQL\BACKUP\master.BAK.
> error:
> e:\data\MSSQL\BACKUP. I had no problem creating the backup devices for
> other system default databases, like msdb, model, etc.
>
>

logical device already exists?

I want to back the master db to the disk file e:\data\MSSQL\BACKUP\master.BA
K.
When I tried to create a new backup device called 'master', I got the error:
Error 15026: Logical device 'master' already exists.
But I've checked several times and don't see master.BAK already exist in e:\
data\MSSQL\BACKUP. I had no problem creating the backup devices for other
system default databases, like msdb, model, etc.
Anybody know what might be the problem?
BingWhen you create a backup device you are associating a logical name to a
physical file name. In SQL Server there is more than one type of device.
If you look in BOL under sysdevices you can find out about all the different
devices. Anyway "master" already is a device that is associated with the
actual master database. If you want to create a logical backup device to
refer to your master database backup, call it "master_bak", or something.
Hope this helps you understand what a device is. If you really want to see
all the devices you already have assigned you can run the following command
from QA:
select * from master.dbo.sysdevices
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"bing" <bing@.discussions.microsoft.com> wrote in message
news:9AC7B842-64A8-4790-8C39-87957075EE39@.microsoft.com...
> I want to back the master db to the disk file
e:\data\MSSQL\BACKUP\master.BAK.
> When I tried to create a new backup device called 'master', I got the
error:
> Error 15026: Logical device 'master' already exists.
> But I've checked several times and don't see master.BAK already exist in
e:\data\MSSQL\BACKUP. I had no problem creating the backup devices for
other system default databases, like msdb, model, etc.
> Anybody know what might be the problem?
> Bing|||Hi,
It seems there is an entry in master..sysdevices table.
Execute the below procedure from Query ANalyzer and ensure that you do not
have the same file.
sp_helpdevice
To double check the same by querying :-
select * from master..sysdevices
If you have an entry then you can drop the device by using the below command
(Execute the comand if you need)
sp_dropdevice 'device_name'
Thanks
Hari
MCDBA
Thanks
Hari
MCDBA
"bing" <bing@.discussions.microsoft.com> wrote in message
news:9AC7B842-64A8-4790-8C39-87957075EE39@.microsoft.com...
> I want to back the master db to the disk file
e:\data\MSSQL\BACKUP\master.BAK.
> When I tried to create a new backup device called 'master', I got the
error:
> Error 15026: Logical device 'master' already exists.
> But I've checked several times and don't see master.BAK already exist in
e:\data\MSSQL\BACKUP. I had no problem creating the backup devices for
other system default databases, like msdb, model, etc.
> Anybody know what might be the problem?
> Bing|||Thanks all for the information. Very helpful.
Bing
"Hari Prasad" wrote:

> Hi,
> It seems there is an entry in master..sysdevices table.
> Execute the below procedure from Query ANalyzer and ensure that you do not
> have the same file.
> sp_helpdevice
> To double check the same by querying :-
> select * from master..sysdevices
> If you have an entry then you can drop the device by using the below comma
nd
> (Execute the comand if you need)
> sp_dropdevice 'device_name'
> Thanks
> Hari
> MCDBA
>
> Thanks
> Hari
> MCDBA
>
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:9AC7B842-64A8-4790-8C39-87957075EE39@.microsoft.com...
> e:\data\MSSQL\BACKUP\master.BAK.
> error:
> e:\data\MSSQL\BACKUP. I had no problem creating the backup devices for
> other system default databases, like msdb, model, etc.
>
>

Friday, February 24, 2012

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
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>

Monday, March 19, 2012

Login failed Error

Login failed Error

Login failed Error

Friday, March 9, 2012

Login account in two servers

Login account in two servers

Wednesday, March 7, 2012

logical device already exists?

logical device already exists?

Friday, February 24, 2012

Logical & Physical files relationships

Logical & Physical files relationships

Logging query errors

Blog Archive

About Me