Steps to Rename a SQL Server Database

By:   |   Comments (26)   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | More > DBA Best Practices


Problem

One of the Junior SQL Server Database Administrator in my company approached me yesterday with a dilemma. He was assigned a task to rename a few of the databases in Beta and Production environments; the reason being the database name was based on some other project that is no longer relevant to the data which is presently stored within the database. At first I started to tell him, but figured it would be smarter to document the same and share the information.

Solution

Database Administrators usually use the sp_renamedb system stored procedure to quickly rename a SQL Server Database. However, the drawback of using sp_renamedb is that it doesn't rename the Logical and Physical names of the underlying database files.

It's a best practice to make sure the Logical Name and Physical File Name of the database is also renamed to reflect the actual name of the database to avoid any confusion with backup, restore or detach/attach operations.

In this tip, you will see the steps which you need to follow to rename a SQL Server Database using the ALTER DATABASE command.

Creating a Sample Database Namely CoreDB

Let's first create a new database named CoreDB using the T-SQL below. Adjust the file locations to match your needs.

USE master
GO

IF EXISTS (SELECT name FROM sys.databases WHERE name = N'CoreDB')
DROP DATABASE CoreDB
GO

USE master
GO

CREATE DATABASE [CoreDB] 
ON PRIMARY 
( 
NAME = N'CoreDB', 
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\CoreDB.mdf' , 
SIZE = 2048KB , 
FILEGROWTH = 1024KB 
)
LOG ON 
( 
NAME = N'CoreDB_log', 
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\CoreDB_log.ldf' , 
SIZE = 1024KB , 
FILEGROWTH = 10%
)
GO

Rename CoreDB Database Using sp_renamedb System Stored Procedure

Now let's rename the CoreDB database to ProductsDB by executing the below T-SQL code.

USE master
GO

ALTER DATABASE CoreDB 
SET SINGLE_USER 
WITH ROLLBACK IMMEDIATE
GO

EXEC master..sp_renamedb 'CoreDB','ProductsDB'
GO

ALTER DATABASE ProductsDB 
SET MULTI_USER 
GO

Once the above T-SQL has executed successfully the database name will change, however the Logical Name and Physical Name will not change. You can verify this by executing the T-SQL below:

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'ProductsDB')
GO

Your output should look something like this from the above query.

database files info

You can see in the above snippet that the Logical Name and Physical Name in the DB File Path column for ProductsDB are still reflecting the old name of CoreDB. This is not a good practice to follow in a Production Environment. Below you will see the steps which a DBA can follow to rename the database and its respective files.

Steps to Rename a SQL Server Database

DBAs should follow the below steps which will not only rename the database, but at the same time will also rename the Logical Name and Physical Name of the database.

This first set of commands put the database in single user mode and also modifies the logical names.

/* Set Database as a Single User */
ALTER DATABASE ProductsDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

/* Change Logical File Names */
ALTER DATABASE [ProductsDB] MODIFY FILE (NAME=N'CoreDB', NEWNAME=N'ProductsDB')
GO

ALTER DATABASE [ProductsDB] MODIFY FILE (NAME=N'CoreDB_log', NEWNAME=N'ProductsDB_log')
GO

This is the output from the above code.

renamed files

Now we need to detach the database, so we can rename the physical files.  If the database files are open you will not be able to rename the files.

/* Detach Current Database */
USE [master]
GO

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

Once the ProductsDB database is detached successfully then the next step will be to rename the Physical Files. This can be done either manually by chaning the file names in the folder where they exist or by using the xp_cmdshell system stored procedure. You can enable xp_cmdshell using the sp_configure system stored procedure.

USE master
GO

/* 0 = Disabled , 1 = Enabled */
sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO

/* 0 = Disabled , 1 = Enabled */
sp_configure 'xp_cmdshell', 1
RECONFIGURE WITH OVERRIDE
GO

Once xp_cmdshell is enabled you can use the below script to rename the physical files of the database.  You might get an Access Denied message if you execute the code below, so here is an article that discusses the access denied issue.  Thank you to Adam Sinclair for providing the link.  If this is the case, it is probably easier to just manually rename the files in the folder.

/* Rename Physical Files */
USE [master]
GO

EXEC xp_cmdshell 'RENAME "C:\Program Files\Microsoft SQL ServerMSSQL10.SQL2008\MSSQL\DATA\CoreDB.mdf", "ProductsDB.mdf"'
GO

EXEC xp_cmdshell 'RENAME "C:\Program Files\Microsoft SQL ServerMSSQL10.SQL2008\MSSQL\DATA\CoreDB_log.ldf", "ProductsDB_log.ldf"'
GO

Now you can disable xp_cmdshell.

USE master
GO

/* 0 = Disabled , 1 = Enabled */
sp_configure 'xp_cmdshell', 0
RECONFIGURE WITH OVERRIDE
GO

/* 0 = Disabled , 1 = Enabled */
sp_configure 'show advanced options', 0
RECONFIGURE WITH OVERRIDE
GO

Once the above step has successfully executed then the next step will be to attach the database, this can be done by executing the T-SQL below:

/* Attach Renamed ProductsDB Database Online */
USE [master]
GO

CREATE DATABASE ProductsDB ON 
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\ProductsDB.mdf' ),
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\ProductsDB_log.ldf' )
FOR ATTACH
GO

Once the above step has successfully executed then the final step will be to allow multi user access for the user database by executing the below T-SQL:

/* Set Database to Multi User*/
ALTER DATABASE ProductsDB SET MULTI_USER 
GO

You can verify the Logical and Physical Names for the ProductsDB database by executing the T-SQL below:

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'ProductsDB')
GO
database files info

You can see in the above snippet that the Logical Name and Physical Name for ProductsDB are now correct.

Next Steps
  • Once the database name is changed successfully then you need to make sure your application code is referring to the new database name.
  • The steps to rename a SQL Server Database mentioned in this tip are applicable for SQL Server 2005 and later.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Ashish Kumar Mehta Ashish Kumar Mehta has been contributing to the MSSQLTips.com community since 2009 with over 60 tips.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Monday, April 27, 2020 - 2:16:59 PM - Greg Robidoux Back To Top (85481)

Hi Diego, you could use dynamic SQL to do this.  Check out this tip.

https://www.mssqltips.com/sqlservertip/1160/execute-dynamic-sql-commands-in-sql-server/

-Greg

Saturday, April 25, 2020 - 1:05:47 PM - Diego Campillo Back To Top (85475)

How can I parameterize? Instead of putting the name of the database, put a parameter

DECLARE @BaseDatosOrigen VARCHAR(50);
DECLARE @RutaBaseDatosMDFOrigen VARCHAR(50);
DECLARE @BaseDatosLOGOrigen VARCHAR(50);
DECLARE @RutaBaseDatosLDFOrigen VARCHAR(50);

SET @BaseDatosOrigen = 'CoreDB';
SET @RutaBaseDatosMDFOrigen = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\CoreDB.mdf';
SET @BaseDatosLOGOrigen = 'CoreDB_log';
SET @RutaBaseDatosLDFOrigen = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\CoreDB_log.ldf';

CREATE DATABASE [CoreDB] 
ON PRIMARY 
( 
NAME = N'CoreDB', 
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\CoreDB.mdf' , 
SIZE = 5120KB , 
FILEGROWTH = 1024KB 
)
LOG ON 
( 
NAME = N'CoreDB_log', 
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\CoreDB_log.ldf' , 
SIZE = 1024KB , 
FILEGROWTH = 10%
)
GO

Thank you so much.

Thursday, January 23, 2020 - 12:59:29 PM - George Back To Top (83959)

Thanks Ashish and to everyone on this post!!

Following the correct order of the tip it worked with my renaming of a production database that in my case it was imperative to conserve the original names. In my personal recomendation, I used the explorer to change the name of the database files and avoid the xp_cmdshell process and the denied permissions.

Thursday, January 3, 2019 - 6:29:00 AM - Greg Robidoux Back To Top (78612)

Thanks everyone for your comments.  The tip has been updated to fix some of the issues.  Let us know if there are any other issues.

Thanks

Tuesday, January 1, 2019 - 2:52:59 PM - Jim Back To Top (78591)

 Thanks Mark!! 

To anyone reading this tutorial, it explains things well but I would just use Mark's code as it works! I tried to use the tutorial, but with the SET OFFLINE to replace the detach database part, but the physical file name didn't seem to change properly. The second ALTER DATABASE [NewDB] MODIFY FILE statement for the .mdf and .ldf files was the missing part.

Jim 

Thursday, June 14, 2018 - 4:13:34 AM - Adam Sinclair Back To Top (76217)

 Thanks Mark!

For the record, here was the missing piece for us to get rid of Access Denied:

The user "NT SERVICE\MSSQLSERVER" needs full permissions on the physical database files that you're trying to rename.

Here's the article that helped us crack that mystery:

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-file-system-permissions-for-database-engine-access?view=sql-server-2017

Tuesday, June 12, 2018 - 5:03:58 PM - Mark Back To Top (76201)

Here's the complete script cobbled together from all the comments and this article:

use master;
ALTER DATABASE [OldDB] MODIFY NAME = [NewDB]
ALTER DATABASE [NewDB] MODIFY FILE (NAME = N'OldDB', NEWNAME = N'NewDB')
ALTER DATABASE [NewDB] MODIFY FILE (NAME = N'NewDB', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\NewDB.mdf')
ALTER DATABASE [NewDB] MODIFY FILE (NAME = N'OldDB_Log', NEWNAME = N'NewDB_Log')
ALTER DATABASE [NewDB] MODIFY FILE (NAME = N'NewDB_Log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\NewDB_log.ldf')
ALTER DATABASE [NewDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE [NewDB] SET OFFLINE

EXEC sp_configure 'show advanced options',1
RECONFIGURE WITH OVERRIDE
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE WITH OVERRIDE

EXEC xp_cmdshell 'RENAME "C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\OldDB.mdf" NewDB.mdf', NO_OUTPUT
EXEC xp_cmdshell 'RENAME "C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\OldDB_log.ldf" NewDB_log.ldf', NO_OUTPUT

EXEC sp_configure 'xp_cmdshell', 0
RECONFIGURE WITH OVERRIDE
EXEC sp_configure 'show advanced options',0
RECONFIGURE WITH OVERRIDE
ALTER DATABASE [NewDB] SET ONLINE
ALTER DATABASE [NewDB] SET MULTI_USER

Sunday, June 10, 2018 - 10:04:38 AM - Adam Sinclair Back To Top (76175)

 Rick Marshall can you post your complete script please?

 

Tuesday, April 17, 2018 - 12:56:10 PM - Bonaventure Biko Back To Top (75721)

 Yay! It worked. Thanks!

 

Friday, March 16, 2018 - 11:30:42 AM - Rick Marshall Back To Top (75448)

 

I also forgot to mention that just prior to executing the code to activate xp_cmdshell, I took the advice of Miguel Quintana and took the CoreDB offline:

ALTER DATABASE [CoreDB] SET OFFLINE 

instead of the following:

 

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

 

Friday, March 16, 2018 - 11:21:53 AM - Rick Marshall Back To Top (75447)

For everything but the most crucial part (renaming the physical files), this works. The problem is with xp_cmdshell privileges in SSMS. There may or may not be typos in the rename logic as well. The key insight is that SSMS has to be in administrator mode (elevated priviledges, SHIFT-CTRL when launching SSMS and say YES to the UAC prompt) for the following code blocks. Here's how I overcame the issues:

Instead of:

-----------------

USE master
GO
sp_configure 'show advanced options'
GO
/* 0 = Disabled , 1 = Enabled */
sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE WITH OVERRIDE
GO

-----------------

I used:

/* Rename Physical Files */
-- show advanced options
EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE

-- enable xp_cmdshell
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE WITH OVERRIDE

SET @CMD = 'RENAME ' + QUOTENAME(@DBPath + @DBOld + '.mdf','"') + ' ' + QUOTENAME(@DBNew + '.mdf','"')
PRINT @CMD
EXEC xp_cmdshell @CMD, NO_OUTPUT

SET @CMD = 'RENAME ' + QUOTENAME(@DBPath + @DBOld + '_log.ldf','"') + ' ' + QUOTENAME(@DBNew+'_log.ldf','"')
PRINT @CMD
EXEC xp_cmdshell @CMD, NO_OUTPUT

EXEC sp_configure 'xp_cmdshell', 0
RECONFIGURE WITH OVERRIDE

-- hide advanced options
EXEC sp_configure 'show advanced options', 0
RECONFIGURE WITH OVERRIDE

--------------------

Also, I changed the RENAME commands from:

/* Rename Physical Files */
USE [master]
GO
EXEC xp_cmdshell 'RENAME "C:\Program Files\Microsoft SQL ServerMSSQL10.SQL2008\MSSQL\DATA\CoreDB.mdf", "ProductsDB.mdf"'
GO
EXEC xp_cmdshell 'RENAME "C:\Program Files\Microsoft SQL ServerMSSQL10.SQL2008\MSSQL\DATA\CoreDB_log.ldf", "ProductsDB_log.ldf"'
GO

---------------

to:

RENAME "C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\CoreDB.mdf" "Products.mdf" RENAME "C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\CoreDB_log.ldf" "Products_log.ldf"
-----------

The important point is no comma and because SSMS is running in Administrator mode, you won't get "Access denied" since you are trying
to rename files in the Program Files Folder. I did update the path for my version of SQL Server 2014 from the example

Thursday, April 20, 2017 - 5:40:55 PM - Mark Freeman Back To Top (55069)

This doesn't work for Windows authentication because the detach changes the file permissions so the renames fail with "Access denied".

Saturday, March 18, 2017 - 12:23:33 PM - larrybud Back To Top (51386)

 

Am I the only one who thinks that it's ridiculous to have to go through this to rename a couple of files?

 

Monday, October 24, 2016 - 10:00:14 AM - Rajesh Jaganatha Back To Top (43622)

Thank you very much for a great post!

Wednesday, July 20, 2016 - 11:37:21 PM - KN1000 Back To Top (41933)

would just like to say thank you.

very helpful post.

this helped me solve my problem or renaming a database and files.

Tuesday, September 8, 2015 - 12:46:00 AM - info.sqldbamail Back To Top (38608)

What can be the minimum downtime if we do this for production db's, What can be the amount of data loss if we detach/set offline?

 

 

Thursday, October 2, 2014 - 6:39:16 PM - Tim B Back To Top (34825)

I actually do this much differently.  I restore the Database from backups to the new Name I want it to be and then Alter the Database with the new name to get the logical files consistent.  Then delete the old Database.  Method works for me...  I do this on a daily basis mind you (environment is a touch different then your typical Database Environment)

Monday, March 24, 2014 - 8:30:17 AM - Raj Back To Top (29843)

good one!

Thursday, September 5, 2013 - 4:06:25 PM - thebrenda Back To Top (26625)

DBCC Freeprocache will clear the cache after a database rename

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/b66354e5-2c00-4396-a8db-a657d5062266/database-rename-problem-with-views-need-help-

Friday, November 30, 2012 - 3:17:13 PM - Mario Back To Top (20676)

I have need to switch two databases, so I made two renames.

But views from some third DB are still reference old DB regardles name (becouse they are compiled with ID), I know if I go to view and alter it (without any real change) it will reference right DB (by name).

Question is, is there way to "rebuild" all views in all DB?

 

regards

Tuesday, November 27, 2012 - 8:44:08 AM - Simon Doubt Back To Top (20563)

Great step-by-step. Thanks very much!

Thursday, July 5, 2012 - 11:24:35 AM - Miguel Quintana Back To Top (18358)

I know this is a very old thread... I was just looking for the Syntax of  sp_renameDB, when I stumbled upon this great article.

Just one small addition. You do not really need to DETACH the database. You can get by with SET OFFLINE.

 


ALTER DATABASE [CoreDB] SET OFFLINE
-- Now rename the physical files
-- Now rename the physical files
-- Now rename the physical files

ALTER DATABASE [CoreDB] SET ONLINE

Friday, December 11, 2009 - 2:05:31 AM - racoonracer Back To Top (4550)

I usually do the following, not sure if it is best practice, but it works fine for me.

1. Detach the database.

2. Rename physical database file

3. Open Query Analyzer (use Master)

4. Type in below to attach the new database name

EXEC sp_attach_db @dbname = N'<new_db_name>',
@filename1 = N'<path to database>\<new_db_name>.mdf',
@filename2 = N'<path to log>\<new_db_name>.ldf'

 5. Change the logical names, file size, growth, etc. in the GUI. (not too strong with sql commands, but I can always update this)


Thursday, December 10, 2009 - 9:18:52 AM - Toby White Back To Top (4538)

This was the way you had to do it on SQL 2000 with the exception of the syntax being different for the attach and detach commands. The easiest way to do this in SQL Server 2005 is just change the physical file names while the database is still online, take the database offline, and then move the physical files and/or change the names, and then set the database back online:

Alter Database DatabaseName set online/offline

Regards

Thursday, December 3, 2009 - 7:16:17 PM - stittdba Back To Top (4515)

If xp_cmdshell was not already enabled on your server, you will want to make sure that you disable it again as part of your clean up from this task.

 

stittdba

Wednesday, December 2, 2009 - 11:01:33 AM - Patrick Wood Back To Top (4511)

Thank you for including this in your tips.  I ran across this problem a few weeks ago and somehow, after quite a few frustrating attempts, found a way to get it done.  I have since forgotten how I did it so your tip is now safely saved where I can find it when I need it the next time.














get free sql tips
agree to terms