Renaming MDF/LDF Files–SQLNewBlogger

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.

2017-06-07 09_38_49-

Then type the name name. In this case, I’m going from WideWorldImporters-SSDT to WideWorldImporters-RR.

2017-06-07 09_38_59-SQLQuery1.sql - (local)_SQL2016.msdb (PLATO_Steve (52)) - Microsoft SQL Server M

That renames the database, but what about the files? If I run this:

sp_helpdb ‘WideWorldImporters-RR’

I get this:

2017-06-07 09_42_00-SQLQuery1.sql - (local)_SQL2016.msdb (PLATO_Steve (53)) - Microsoft SQL Server M

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.

2017-06-07 09_43_29-Database Properties - WideWorldImporters-RR

However, if I scroll to the right to the File Name column, I can’t change anything.

2017-06-07 09_43_44-SQLQuery1.sql - (local)_SQL2016.msdb (PLATO_Steve (53))_ - Microsoft SQL Server

What I need to do is use the ALTER DATABASE command with the MODIFY FILE command. I need to do this twice.

  1. Change the physical file name
  2. 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.

2017-06-07 09_48_38-SQLQuery1.sql - (local)_SQL2016.WideWorldImporters-RR (PLATO_Steve (53))_ - Micr

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.

2017-06-07 09_53_35-DATA

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.

2017-06-07 09_54_13-SQLQuery1.sql - (local)_SQL2016.master (PLATO_Steve (53))_ - Microsoft SQL Serve

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.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s