Renaming SQL Server Database

Very recently I was working on a data archival module. Project was almost 10+ years old and it kept very sensitive, huge data set. While testing this, accidentally I have deleted some required records from a shared development database which is not awesome at all. Only option that I had was restoring backup files.

But developers who worked with me requested to keep the damage database, since they were not sync schema with the source controller. Only possibility that came to my mind was rename the damaged database and restore backup database with the original name.

As all ways I googled it "how to rename a SQL server database", many articles were listed down in my search result but couldn't find a good all in one article. therefore thought of sharing what I learned.

Understanding what we are going to do with the rename is important.Mainly this process can divided into three parts,

1. Rename Database
2. Rename Mappings
3. Rename Files

Following the order of above three parts are necessary. And if you see closely renaming a database will leads to three different renames.  

Below image indicates very low level picture of the components that we are going to rename

Rename Database

Below image indicates Original database,
Right click on the database -> Select Properties -> Select Files tab.

This will be the first step. Once we completed this part, database rename will be visible in the SQL Server Management Studio - Object Explorer. Let's rename the database.

USE master
GO

ALTER DATABASE OriginalDB 
SET SINGLE_USER 
WITH ROLLBACK IMMEDIATE
GO

EXEC master..sp_renamedb 'OriginalDB','RenameDB'
GO

ALTER DATABASE RenameDB 
SET MULTI_USER 
GO

Special point you can see in the code is set user accessibility to single user before going to rename the database. Once rename is completed alter database back to multi user accessibility. This will ensure withing that time period no one else can access the database.

Rename Mappings

Result of below query excution gives you the logical file names of the database files.

USE master
GO

-- Identify Database 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'RenameDB')
GO

Though we renamed the databases, Logical files are yet to rename. That will be the second step. In this step again you have to alter database user accessibility to single. Probably you have noticed that I haven't alter it to multi user again. I have done this purposely because till we complete entire process we cannot gran this permission again.

-- Set Database as a Single User --
ALTER DATABASE [RenameDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

-- Change Logical File Name --
ALTER DATABASE [RenameDB] MODIFY FILE (NAME=N'OriginalDB', NEWNAME=N'RenameDB')
GO
ALTER DATABASE [RenameDB] MODIFY FILE (NAME=N'OriginalDB_log', NEWNAME=N'RenameDB_log')
GO

After successful execution you can re-run the above query (Query that list down database file information). Files should be renamed to your new names. But still you can see the physical file with the old name. Next step is to rename that.

Rename Files

As you can see, physical files of logical files are still in the original name. Next thing is to rename the physical file names. Navigate to DB File Path. Find physical files.

Before you rename make sure you detached the database first.

/* Detach Current Database */
USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'RenameDB'
GO

Once you detached the database you can rename these files using xp_cmdshell in SQL Server or manually from the file exporer (use desired way). In my case I used manual renaming.

When file rename completed next is to re attached the database. Below query attached database back. Also make sure to grant multi user accessibility back.

/* Attach Renamed RenameDB Database Online */
USE [master]
GO
CREATE DATABASE RenameDB ON 
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\RenameDB.mdf' ),
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\RenameDB_log.ldf' )
FOR ATTACH
GO

ALTER DATABASE RenameDB 
SET MULTI_USER 
GO

To ensure everything renamed properly you can re-run the query that shows logical file names of the database.

This will end a successful database rename :)

May the force be with you!