Changing the Logical File Name

I have a base database that I use to create other databases occasionally.

I notice that when I look at one of my created databases, from the base, that it has the LogicalName of the base. I thought maybe this wasn't a big deal, but then read:
>> The logical name uniquely identifies a file within a database for use in commands like ALTER DATABASE.  

That now sounds dangerous. I have several databases with the same logical name. Should I change those? I'd like to.

If so, how?

Starr DuskkASP.NET VB.NET DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Steve WalesSenior Database AdministratorCommented:
Should you?  That's up to you.

It identifies the data file within the database.  You could have 100 databases and have the same LOGICAL file name of "Fred_Flintstone" for all.  Wouldn't really matter.

If you WANT to rename it, see the online docs:

ALTER DATABASE [mydb] MODIFY FILE (NAME = oldname, NEWNAME = newname);

Pretty sure that's how you'd do it.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott PletcherSenior DBACommented:
Agreed.  It's not really dangerous, although I guess you could accidentally run a command on the wrong db by mistake since the logical names are the same.

It's most common to see the logical names match the db name, but, as Steve noted, SQL certainly doesn't require it.  SQL doesn't care what the logical names are as long as they are unique within the same db.

[Similarly, btw, SQL doesn't care what data file extensions you use.  ".mdf" and ".ldf" are considered standard, but SQL wouldn't care if you used .qqq or .xls.]
Steve WalesSenior Database AdministratorCommented:
Thanks for pointing that out, Scott.  Should have mentioned it myself - I tend to include the DB names in the logical file names of my databases - and data file names for that matter - just so there's no mistakes down the road.

As for your last point, SQL doesn't care ... but you'd confuse the blazes out of Excel if it tried to open a DB file called .xls :)
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Scott PletcherSenior DBACommented:
I once renamed some db files on the dev system that way just to drive the new junior DBA crazy :-).
Starr DuskkASP.NET VB.NET DeveloperAuthor Commented:
Starr DuskkASP.NET VB.NET DeveloperAuthor Commented:
>>ALTER DATABASE [mydb] MODIFY FILE (NAME = oldname, NEWNAME = newname);

Okay, so if I have my original SSBase logical file name, and I copy it and the new one has the same name, when I do the ALTER DATABASE SSBase, how does it know which one it is altering?

Steve WalesSenior Database AdministratorCommented:
You would do:

use [DB_Name_You_Want_to_Modify]
alter database ...
Starr DuskkASP.NET VB.NET DeveloperAuthor Commented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.