Rename Database

Today was the proof that I have a lot to learn about DBA tasks. I took on a somewhat simple task of renaming a database that turned into a 30-minute ordeal. Hopefully, this post will save you some time by avoiding issues that I ran into. Below I outlined one of the possible ways of getting this task done. Please note that this can result in outage and should not be done in production (live) environment, unless you are confident in what you are doing and it is done during a maintenance window.

Step 1

Before we can rename DB we need to identify Logical and Physical file names. I recommend copying result to notepad or somewhere else as it will be easier to paste it into later queries. To get names we can query system table in master DB. In my scenario, my Database Name is "VladTesting"

 
USE [master]
GO
/* Important to get logical and physical file names */

SELECT  name AS [Logical Name]
       ,physical_name AS [DB File Path]
       ,type_desc AS [File Type]
       ,state_desc AS [State]
FROM    sys.master_files
WHERE   database_id = DB_ID(N'VladTesting')
GO

Logical Name	DB File Path	File Type	State
VladTesting	C:\SQL Server Data Files\MSSQL11\Data\VladTesting.mdf	ROWS	ONLINE
VladTesting_log	C:\SQL Server Data Files\MSSQL11\Log\VladTesting_log.ldf	LOG	ONLINE

Step 2

Now we are ready to start the process. Before logic names can be renamed we need to have exclusive rights to DB.

 
ALTER DATABASE VladTesting 
SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Step 3

Now when we execute the following statement it will rename logical file names.

 
ALTER DATABASE [VladTesting] MODIFY FILE 
(NAME=N'VladTesting', NEWNAME=N'VladsAwesomeDB')
GO
ALTER DATABASE [VladTesting] MODIFY FILE 
(NAME=N'VladTesting_log', NEWNAME=N'VladsAwesomeDB')
GO

Step 4

At this point we have several options, the following is one of the easiest. We need to DETACH database, rename files and than attach it with new name later.
Another option is to take DB Offline and rename files and later rename DB.

 
EXEC master.dbo.sp_detach_db  @dbname = N'VladTesting'
GO

Step 5

Now to rename the files it can be done from SSMS with TSQL or directly on file system. NOTE: When I ran the following command it messed up file permissions, this should be used with caution. It is safer to ask System Admins or someone else with proper file system permissions to rename files directly on file system, then it is possible to shift the blame if something goes wrong (just kidding).
Most of the time XP_CMDSHELL is not enable as it is considered dangerous, to enable it, just run the following code.

 
EXEC sp_configure 'show advanced options' ,1;   
GO  
RECONFIGURE;  
GO  
EXEC sp_configure 'xp_cmdshell' ,1;  
GO  
RECONFIGURE;  
GO 

Step 6

Now to actually rename the files, we simply provide old file name with path and new filename to the XP_CMDSHELL command.
In order to execute rename command we first need to enable XP_CMDSHELL if it is not already enabled.

 
EXEC xp_cmdshell 'RENAME "C:\SQL Server Data Files\MSSQL11\Data\VladTesting.mdf", "NewDatabaseName.mdf"'
GO
EXEC xp_cmdshell  'RENAME "C:\SQL Server Data Files\MSSQL11\Log\VladTesting_log.ldf", "NewDatabaseName_log.ldf"'
GO

Step 7

Now for the last step. We attach DB with new name and new physical file names that we just renamed.

 
CREATE DATABASE VladsAwesomeDB ON 
( FILENAME = N'C:\SQL Server Data Files\MSSQL11\Data\VladsAwesomeDB.mdf' ),
( FILENAME = N'C:\SQL Server Data Files\MSSQL11\Log\VladsAwesomeDB_log.ldf' ) FOR ATTACH
GO
ALTER DATABASE VladsAwesomeDB SET MULTI_USER 
GO

Step 8 - Housekeeping

Since xp_cmdshell considered dangerous it is best to disable before we forget that it had to be enabled. First command will disable xp_cmdshell and second command will hide advanced options.

 
EXEC sp_configure 'xp_cmdshell' ,0;  
GO  
RECONFIGURE;  
GO 
EXEC sp_configure 'show advanced options' ,0;   
GO  
RECONFIGURE;  
GO

---

So now you know how to rename database. Usually this should only be done when there is specific business reason to do so. If command sp_renamedb or ALTER DATABASE Modify Name is used, it does not rename physical or logical files which can cause issues later when someone tries to create new database with same name as last one or someone does restore of backup without changing options.