|
Home > Archive > MS SQL Server > October 2006 > What is the 'logical' file name?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
What is the 'logical' file name?
|
|
| kevin bailey 2006-10-24, 6:36 pm |
| What is the 'logical' file name?
When restoring over a different database I use the WITH MOVE option to
change the name of the physical file. But what is the logical file name?
I don't alter it and the restore seems to work fine but I'd like to know
what it is exactly.
Thanks,
Kevin
| |
| Warren Brunk 2006-10-24, 6:36 pm |
| Taken right out of BOL...
Specifies that the given logical_file_name should be moved to
operating_system_fil
e_name. By default, the logical_file_name is restored to
its original location. If the RESTORE statement is used to copy a database
to the same or different server, the MOVE option may be needed to relocate
the database files and to avoid collisions with existing files. Each logical
file in the database can be specified in different MOVE statements.
Basically you are saying what the Database Filename is and directing it a
new path.
You can use
use <database_name>
go
sp_helpfile
go
to determine the name of the logical filename.
Does that help?
--
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"kevin bailey" < kbailey@freewayproje
cts.com> wrote in message
news:egh564$63v$1$83
02bc10@news.demon.co.uk...
> What is the 'logical' file name?
>
> When restoring over a different database I use the WITH MOVE option to
> change the name of the physical file. But what is the logical file name?
>
> I don't alter it and the restore seems to work fine but I'd like to know
> what it is exactly.
>
> Thanks,
>
> Kevin
>
>
| |
| Kalen Delaney 2006-10-24, 6:36 pm |
| Hi Kevin
Each file in a SQL Server database has two names, the logical and physical.
The physical name is the actual OS file name, with path information. The
logical name is a basically just the handle you use when referring to a
file, when running SQL Server commands. For example, if you want to change
the properties of a file, you specify the file by using its logical name.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"kevin bailey" < kbailey@freewayproje
cts.com> wrote in message
news:egh564$63v$1$83
02bc10@news.demon.co.uk...
> What is the 'logical' file name?
>
> When restoring over a different database I use the WITH MOVE option to
> change the name of the physical file. But what is the logical file name?
>
> I don't alter it and the restore seems to work fine but I'd like to know
> what it is exactly.
>
> Thanks,
>
> Kevin
>
>
| |
| kevin bailey 2006-10-24, 6:37 pm |
| Warren Brunk wrote:
> Taken right out of BOL...
> Specifies that the given logical_file_name should be moved to
> operating_system_fil
e_name. By default, the logical_file_name is restored
> to its original location. If the RESTORE statement is used to copy a
> database to the same or different server, the MOVE option may be needed to
> relocate the database files and to avoid collisions with existing files.
> Each logical file in the database can be specified in different MOVE
> statements.
>
> Basically you are saying what the Database Filename is and directing it a
> new path.
>
> You can use
> use <database_name>
> go
> sp_helpfile
> go
>
> to determine the name of the logical filename.
>
>
> Does that help?
>
Seems that each database gets its own physical files. And these files also
have second names called a 'logical' file name.
So if I restore a database from a backup to overwrite an existing DB I have
to use WITH MOVE to specify the new physical file name which needs to match
the physical file name of the DB being overwritten.
As for the logical file name - ermmmm. I seem to use the logical file name
which is stored on the backup (I think the backup is read by the restore
applet) in the WITH MOVE statement.
It seems to work but what on earth it should be I'm not sure. Does this
mean the restored DB has a new logical file name which is the one from the
backup. Or should I really use the logical filename for the DB before it
was overwritten? How do I find the logical filename?
And anyway - if an enterprise level DBMS like Postgresql can carry out
backups, restores etc etc etc without referring to the underlying file
names why does MS SQL need this added complexity/cost? What benefit is
there?
Thanks,
Kevin
| |
| Tibor Karaszi 2006-10-24, 6:37 pm |
| > Seems that each database gets its own physical files. And these files also
> have second names called a 'logical' file name.
Correct. See the CREATE DATABASE command and you will see this clearly.
> So if I restore a database from a backup to overwrite an existing DB I have
> to use WITH MOVE to specify the new physical file name which needs to match
> the physical file name of the DB being overwritten.
Think of it this way:
When you RESTORE, SQL Server will read the backup header to determine what files were used.
If a database with same name and same file layout exists, then SQL Server just read the pages from
the backup media and put the pages in the same place as they were when you took the backup.
If there is no database with the name you try to restore, then SQL Server will internally execute
the CREATE DATABASE command for you and then read in the pages from the backup media.
If a database with same name and but different file layout exists, you'll get an error message.
Unless you specify REPLACE, which mean that SQL Server will internally do DROP DATABASE, then CREATE
DATABASE and then read in the pages from the backup media.
You use the MOVE option when you want to specify a different physical file name (path, for instance)
compared to what you had when you performed the backup.
> It seems to work but what on earth it should be I'm not sure. Does this
> mean the restored DB has a new logical file name which is the one from the
> backup.
The logical name in the restored database comes from the backup, yes. A restored database should
look exactly like it did when you took the backup, including the logical file names. You can change
the logical file names for database files in an existing database using ALTER DATABASE.
> Or should I really use the logical filename for the DB before it
> was overwritten?
Not sure I understand that question. There is no problems having two database in same SQL Server
instances with same logical file names. But you might want to change this for aesthetic reasons.
> How do I find the logical filename?
For an existing database? sp_helpdb or look in the system tables/catalog views.
For a backup device? Use RESTORE FILELISTONLY.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
"kevin bailey" < kbailey@freewayproje
cts.com> wrote in message
news:eglfa3$k07$1$83
00dec7@news.demon.co.uk...
> Warren Brunk wrote:
>
>
>
> Seems that each database gets its own physical files. And these files also
> have second names called a 'logical' file name.
>
> So if I restore a database from a backup to overwrite an existing DB I have
> to use WITH MOVE to specify the new physical file name which needs to match
> the physical file name of the DB being overwritten.
>
> As for the logical file name - ermmmm. I seem to use the logical file name
> which is stored on the backup (I think the backup is read by the restore
> applet) in the WITH MOVE statement.
>
> It seems to work but what on earth it should be I'm not sure. Does this
> mean the restored DB has a new logical file name which is the one from the
> backup. Or should I really use the logical filename for the DB before it
> was overwritten? How do I find the logical filename?
>
> And anyway - if an enterprise level DBMS like Postgresql can carry out
> backups, restores etc etc etc without referring to the underlying file
> names why does MS SQL need this added complexity/cost? What benefit is
> there?
>
> Thanks,
>
> Kevin
| |
| kevin bailey 2006-10-24, 6:37 pm |
| Tibor Karaszi wrote:
>
> Correct. See the CREATE DATABASE command and you will see this clearly.
>
>
>
> Think of it this way:
>
> When you RESTORE, SQL Server will read the backup header to determine what
> files were used. If a database with same name and same file layout exists,
> then SQL Server just read the pages from the backup media and put the
> pages in the same place as they were when you took the backup.
>
> If there is no database with the name you try to restore, then SQL Server
> will internally execute the CREATE DATABASE command for you and then read
> in the pages from the backup media.
>
> If a database with same name and but different file layout exists, you'll
> get an error message. Unless you specify REPLACE, which mean that SQL
> Server will internally do DROP DATABASE, then CREATE DATABASE and then
> read in the pages from the backup media.
>
> You use the MOVE option when you want to specify a different physical file
> name (path, for instance) compared to what you had when you performed the
> backup.
>
>
>
>
> The logical name in the restored database comes from the backup, yes. A
> restored database should look exactly like it did when you took the
> backup, including the logical file names. You can change the logical file
> names for database files in an existing database using ALTER DATABASE.
>
>
>
> Not sure I understand that question. There is no problems having two
> database in same SQL Server instances with same logical file names. But
> you might want to change this for aesthetic reasons.
>
>
> For an existing database? sp_helpdb or look in the system tables/catalog
> views. For a backup device? Use RESTORE FILELISTONLY.
>
>
Thanks Tibor for helping to understand these points.
|
|
|
|
|