Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.
I would guess many people would run into this situation at some point. A developer or DBA creates a database, then decides to rename it, but the logical and physical names aren’t correct. This post will look at how to do this. A couple of notes and then the process below.
This might not matter to many of you, but in development, I sometimes find I’ll rename a database and then attempt to recreate (or deploy) a new database with the old name. The mdf/ldf files don’t match, and I realize it’s because I’m using defaults.
However, I’d also say this is an issue in a DR situation. If the filenames don’t seem to match, someone might restore the wrong database or the wrong files. Or worse, think the can delete a file on the file system because there’s no database with that name.
Renaming the Database
This is easy. Right click, select Rename.
Then type the name name. In this case, I’m going from WideWorldImporters-SSDT to WideWorldImporters-RR.
That renames the database, but what about the files? If I run this:
sp_helpdb ‘WideWorldImporters-RR’
I get this:
Not really what I want. I need these mdf/ldf files to be changed. How do I do this?
I can get to the properties for the database and select the “Files” pane to get a list of files. Here I can change the logical name by clicking that field and typing a new name. I’ve done that here.
However, if I scroll to the right to the File Name column, I can’t change anything.
What I need to do is use the ALTER DATABASE command with the MODIFY FILE command. I need to do this twice.
- Change the physical file name
- Change the logical file name
Let’s do that. Here’s the code to change the physical name.
ALTER DATABASE [WideWorldImporters-RR]
MODIFY FILE
( NAME = 'WideWorldImporters-SSDT_Data',
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA\WideWorldImporters-RR.mdf'
);
I need to repeat this for the log file and the MOT file. Once I change the names, I get this message.
This is key. If I were to restart my system now, when the database attempted to start and go through recovery, the files would not have been found. Now, I need to change the physical file names.
To do that, I first need to take the database offline.
USE master
go
ALTER DATABASE [WideWorldImporters-RR] SET OFFLINE
Then I go to the location of the physical files and rename them in Windows Explorer.
Now I bring the database online.
ALTER DATABASE [WideWorldImporters-RR] SET ONLINE
Once that’s done, I can then use ALTER DATABASE again to change the logical file names.
ALTER DATABASE [WideWorldImporters-RR]
MODIFY FILE (NAME='USERDATA_612671E2',
NEWNAME = 'WWI_UserData'
);
And run a final sp_helpdb.
SQLNewBlogger
An easy task, with a touch of research in Books Online, but not too difficult. This took me about 10 minutes to do, and since I realized this was a good skill, I took screenshots and saved code as I went.
Then about 10 minutes to write this up.
