Renaming a database isn’t something you do frequently but it does have bit of a gotcha. First of all let’s go over how you actually rename the database. It’s pretty simple.

SSMS

Right click on the database name in the object explorer and select rename or highlight the name and hit F2. Then type over the old name with the new one.

T-SQL

ALTER DATABASE CurrentDBName MODIFY NAME = NewDBName;

Personally I find the code a little bit odd since for the most part when modifying a database using ALTER DATABASE you use the SET keyword not MODIFY but it’s not all that big a deal.

So here is where the gotcha comes in. Let’s say you create a database in your test environment called Application_Dev.

CREATE DATABASE Application_Dev

Once you are done you decide to back it up and restore it to your test environment. Once the database is in the new environment you rename it to Application_Test. And so on.

Side note here.

1. I don’t recommend the practice of naming your databases _dev, _test etc. It does make you a little less likely to run code in the wrong environment but it becomes a real problem if you ever need to reference something using a three or four part name. Application_Dev.dbo.table for example. You will have to modify your code in each environment and in general I want every piece of code to be identical between environments if at all possible.
2. Backing up and restoring to different environments is handy and I will frequently use for the initial move of a database between SDLC stacks. After that first move however, I recommend using a version control tool, a comparison tool or scripting even though your database may not be live yet. It’s to easy to forget minor differences (particularly in data) between the different databases and that can cause you some problems.

Some time later one of your co-workers is looking at the database files for Application_Prod and notes that the files are named Application_Dev.mdf and Application_Dev_log.ldf. Your co-worker is either going to be very confused, and possibly concerned that the database isn’t correct or amused by the disconnect of the difference between the database name and the file names. Also if the difference is big enough (say the file names are POC.mdf and POC.ldf this could cause a problem for someone looking in the data directory and not through SSMS.

In case you were interested you can rename the files too but it isn’t nearly as simple of a process. There may be an easier way to do this but I haven’t been able to find it.

First you rename the files within SQL.

ALTER DATABASE Test
MODIFY FILE ( 
NAME = LogicalFileName, 
FILENAME = 'NewPath\NewFileName'
-- For example : 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014CI\MSSQL\DATA\NewMDFName.mdf'
)

Then you have to detach your database

USE master
GO
EXEC sp_detach_db 'DBName';
GO

Then rename your file in the file system and attach it.

USE master
GO
EXEC sp_attach_db 'Test',
'NewPath\NewFileName1',
'NewPath\NewFileName2';
GO

I’m not really a fan of detaching and re-attaching a database but in this case it appears to be the only way. Oh, and last but not least changing the logical name of the files just to be consistent.

ALTER DATABASE Test
MODIFY FILE ( 
NAME = OldLogicalName, 
NEWNAME = NewLogicalName
)

Filed under: Microsoft SQL Server, SQLServerPedia Syndication, SSMS Tagged: database files, microsoft sql server