Untangle Database Names

Generally, when you set up a database, you call it 'SALESDB' or whatever, and that's the end of it.  The logical file names will default to SALESDB.MDF AND SALESDB_log.LDF, and so will the physical names on disk.  But over the years those names can get changed.  In my case I am in the middle of migrating databases from one system to another, and part of that involves changing the names which were created by a company which was taken over - you could consider it as airbrushing traces of the old system out of existence, I couldn't possibly comment. 

I found a good explanation in this MSSQLTip.    In their case, the project had changed and the old name was no longer relevant.  It could be a typo that irritates you enough to do something about it.  It could be that you are anally retentive enough that, like me, you like your database names to be consistent.  

Consistent?  Well, consider - the database name that you see every day isn't necessarily the underlying logical name of the database files.  Still less can you be sure that it is the name of the physical .mdf and .ldf files on disk.   Here's the Official word.  But what this means is that there can be three names - the name of the database, the logical name of the database files, and the physical name of the files on the disk.  They can all be different if you want them to be.

Caution - that way lies madness...

What I'll try to do in this post is untangle these various names and explain how to change them to be consistent.  SQL Server is reasonably easygoing as to what you call your database - but be sensible about it.  

First of all, get details of the database. The stored procedure sp_helpdb provides quite a lot of information about the database, (more than is shown here) but the results below are what we are interested in.  The name in square brackets is the name of the database.  Column 1 contains the logical names of the two database files.  Column 3 contains the physical names of the .mdf and .ldf files as they are found on the disk. Save this somewhere handy like an Outlook Task - you'll want to refer to it later. 

exec sp_helpdb [DQTHL]

DQTHL                 1              E:\MSSQL\Data\DQTHL.mdf

DQTHL_log          2              F:\MSSQL\Log\DQTHL_log.ldf

Let's all dance around singing halleluiah! Everything is consistent - the logical and physical names all match the database name.

If it was always so, there would be no point to this blog.  But it ain't like that all the time.  In a sense, it doesn't really matter - SQL Server would tolerate my next example, even though the database name doesn't match the logical file names, and the physical file names are something entirely unrelated.   It keeps track of what things are called and where they are.  The problem comes when a human being gets involved - maybe you want to do some housekeeping, and discover that the names don't tie up.  Humans are not good at telling which file belongs where, especially if there are lots of them, all with similar names. 

exec sp_helpdb [DQTHL]

  

DQ_Data                1              E:\MSSQL\Data\OldSales.mdf
DQ_log                  2              E:\MSSQL\Log\OldSales_log.ldf

OK, first step is to make sure that nobody else is doing something with the database.
You might get away without bothering, but you know what Sod's Law is like...

-- get exclusive access

alter database  [BEC_BASES]

SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Go

It's easy to change the database name - just right click on it and Rename is one of the options.  There's also a stored procedure you can use:

EXEC sp_renamedb 'old_name' , 'new_name'

 

However, I have a feeling that this is deprecated (meaning that Microsoft will wait until the most inconvenient time for you and then drop it).  Use Alter Database instead:

--Change DATABASE name

ALTER DATABASE  [BEC_BASES]

       MODIFY NAME = [BASES]

Go

Then we need to change the logical name of the files.  Remember we did sp_helpdb a few minutes ago?  This will give you the current names.  Almost always you will have an MFD and and LDF file,  but you could also have one or more NDF files as well, and in fact you can use any suffix you want. I came across a database where one of my predecessors had renamed the LDF file as MDF, so that there were two MDF files on the disk and no LDF files. 

Caution - that way lies madness...

-- Change Logical name

ALTER DATABASE [BASES] MODIFY FILE (NAME=N'BEC_BASES', NEWNAME=N'BASES')

GO

ALTER DATABASE [BASES] MODIFY FILE (NAME=N'BEC_BASES_log', NEWNAME=N'BASES_log')

go

Then we want to change the names of the physical files.  I used to Detach the database, change the file names, and then re-attach, and that's fine, but I think this way is actually easier.

Let go of the database (or you won't be able to do the next bit):

use master

go

Take the database offline

ALTER DATABASE BASES SET OFFLINE

GO

Tell it the file names that you are going to use:

ALTER DATABASE BASES MODIFY FILE (NAME = BASES, FILENAME = 'E:\MSSQL\Data\BASES.mdf')

GO

ALTER DATABASE BASES MODIFY FILE (NAME = BASES_log, FILENAME = 'F:\MSSQL\Log\BASES_log.ldf')

GO

The files are modified in the system catalog. The new path will be used the next time the database is started.

Now go to File Explorer or however you like to carry out operating system tasks like Rename.  I suppose in theory that you could do this with XP_CMDShell, but you would have to remember to enable it and then disable it again afterwards - more trouble than it's worth.

Navigate to the data folder and rename the MDF file, then to the log folder and rename the LDF file. 

Put the database back online

ALTER DATABASE BASES SET ONLINE

GO

Open up the access to everyone (remember you made it single user?)

ALTER DATABASE [BASES]

       SET MULTI_USER

And just to be sure that you have done everything right, run sp_helpdb again and check the results against your original:

exec sp_helpdb [BASES]

DQTHL                 1              E:\MSSQL\Data\DQTHL.mdf

DQTHL_log          2              F:\MSSQL\Log\DQTHL_log.ldf

We've untangled it!  Let's all dance around singing halleluiah!