October 18, 2011 at 7:51 am
OK, I'm trying to sort out what the "logical name" of a database is used for. My employer is getting ready to deploy an application which in some situations will require multiple copies of the "base" DB exist on one server. The original (and ugly) plan was for the tech who was installing the application to detach the base DB, copy it, re-attach the base, rename the copied files (say to appDB01.mdf / ldf) then attach the copy as appDB01. It seems we can do this much easier by running a full backup of the base DB, then restoring the backup to a new DB.
BUT the "logical name" of the DB remains the same. It's easy enough to change (the techs will be using the SSMS GUI,) but I find myself wondering what it is used for? Is it simply a way for it to be referred to in an application, or does it server little useful purpose like an appendix?
Thanks,
Jason A.
October 18, 2011 at 7:54 am
Logical name of the database (which has to be unique within an instance) or logical names of the database files (which have to be unique within the database)?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 18, 2011 at 8:00 am
Ease is relative. A backup restore requires double the disk space of the data (backup file + database mdf/ldf). An attach requires the space only for the mdf/ldf.
The physical name of the files is meaningless. You can have myDB.mdf and myOldDB.ldf as the files and call the database anything you like in SSMS/SQL Server, but the names of each database, the "logical name" you see in SSMS, is unique in each instance.
If you need multiple copies of the db on a server, then you need multiple instances, or different names for each database. Different physical files as well, but you could easily copy the mdf/ldf files multiple times and then attach each copy as AppDB01, AppDB02, etc.
October 18, 2011 at 8:00 am
The Logical Name field in question, is in:
Database properties->Files
See the attached JPG, the section in question is in the red box.
I'm going to presume from where I found this, it would be the
logical names of the database files
Thanks!
Jason A.
October 18, 2011 at 8:06 am
That's the files names. They have to be unique in a database only, anywhere they're used they're either used with the database name or only for the currently selected database.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 18, 2011 at 2:53 pm
Thanks!
December 16, 2011 at 11:58 am
OK please explain this to me..
I have >20 dbs on one instance. When they are attached they have different db names now when I go the db properties, all the dbs have the exact same logical name but the physical name are different. I thought each db had to have different logical name and if this is the case what are the pros and cons of having dbs created like this?
PS I was called in to assist this company with Log Shipping and other DBA admin duties, but I've never ran across an environment like this one.
When I attempt to configure Log Shipping after the 1st db the error returned is that this DB already exists and cannot not be overwritten
December 16, 2011 at 12:14 pm
Logical names of the databases the same (databases only have one name) or logical names of the files the same?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 16, 2011 at 12:49 pm
The Database files logical Name are the same for all 20 databases but the file names are all different
December 16, 2011 at 1:25 pm
Nothing wrong there. The logical names for files only have to be unique within a database.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply