Rename a SQL Server database AND its data and log files

Just renamed a database for a customer, and I wanted to share the logic with all of you. When we rename a database, it only effects the database name itself.  It does not change the data or log file names. I HATE it when the data/log file names don't match the db name! Consistently named objects are faaaar easier to manage, imo.  :-)  You can use this method to rename the database AND its data and log files.  Easy peasy.  In this example, the 'InformDVODV' database is renamed to 'InformDVOPD'.

-- get your db filenames

USE InformDVODV; -- current database name

EXEC sp_helpfile

-- physical names and paths

-- only change the data/log file names in the FILENAME value

ALTER DATABASE InformDVODV -- don't change

MODIFY FILE (

       NAME = 'InformDVODV', -- don't change

       FILENAME = 'E:\MSSQL\DATA\InformDVOPD.mdf' -- change this

);

ALTER DATABASE InformDVODV -- don't change

MODIFY FILE (

       NAME = 'InformDVODV_log', -- don't change

       FILENAME = 'O:\MSSQL\Log\InformDVOPD_log.ldf' -- change this

);

-- logical names

-- only change the NEWNAME value

ALTER DATABASE InformDVODV MODIFY FILE (

NAME = InformDVODV,

NEWNAME = InformDVOPD -- change this

);

ALTER DATABASE InformDVODV MODIFY FILE (

NAME = InformDVODV_log,

NEWNAME = InformDVOPD_log -- change this

);

-- now we rename the database

USE master; 

GO 

ALTER DATABASE InformDVODV 

Modify Name = InformDVOPD; 

GO 

-- check your newly renamed files

USE InformDVOPD; -- new db name

EXEC sp_helpfile

That's it!  Important to know, you cannot do this if users are in the database.  If there are active connections, you will need to kill them before attempting the rename. Run this to kill any connections by setting the database to single user mode:

USE [master];

GO

-- disconnect all existing sessions 

ALTER DATABASE InformDVODV SET SINGLE_USER WITH ROLLBACK IMMEDIATE

GO

 And you'll run this after you've renamed the database:

-- change to multi-user

ALTER DATABASE InformDVOPD SET MULTI_USER

GO

That's it!  Follow the sequence and double-check your filenames, this will work fine. I would recommend taking a look at both of these for more details as well:

ALTER DATABASE

https://msdn.microsoft.com/en-us/library/ms174269.aspx#Remarks

RENAME DATABASE

https://msdn.microsoft.com/en-us/library/ms345378.aspx