How to rename a SQL Server database

By:   |   Updated: 2022-02-25   |   Comments (26)   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | More > Database Administration


Problem

Sometimes there is a need to change the name of a database whether this is because the original name was based on some other project that is no longer relevant to the data stored in the database or maybe it was because you restored a database and at the time just gave it a temporary name, but long term it ended up being a database that needed to remain on your system.

Regardless of the reason, there may come a time when you want to or need to rename a database. In this tip we look at a couple different options.

Solution

There are a few ways of doing this. Let's say I have a database named "Test" and I want to rename to "Test2".

Option 1 - Rename SQL Server Database using T-SQL

This command works for SQL Server 2005, 2008, 2008R2, 2012, 2014, 2016, 2017 and 2019:

ALTER DATABASE [Test] MODIFY NAME = [Test2]

If you are using SQL Server 2000 you can use the T-SQL command below to make the database name change. This still works for SQL 2005, 2008, 2008R2, 2012, 2014, 2016, 2017 and 2019, but Microsoft says it will be phased out at some time.

EXEC sp_renamedb 'Test', 'Test2'

Option 2 - Rename SQL Server Database using SSMS rename option

If you are using SQL Server Management Studio, right click on the database and select the Rename option and then rename the database.

rename database using ssms

Option 3 - Rename SQL Server Database using SSMS

Another simple way to rename the database is to just click on the database name in the Object Explorer and rename the database like you would rename a folder in Windows.

rename database using ssms

Option 4 - Rename SQL Server database using detach and attach

Use the detach and attach feature of SQL Server to detach the database first and when you reattach the database you give the database a different name. This can be done by using the following T-SQL commands (enter your database name and info).

First run the following to get the database file names:

EXEC sp_helpdb 'Test'

First detach the database:

EXEC sp_detach_db 'Test', 'true'

Then attach the database using the files from the first command above:

EXEC sp_attach_db
@dbname = N'Test2',
@filename1 = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\test.mdf',
@filename2 = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\test_log.ldf'

Detach and Reattach using SSMS

You can also do this using the SSMS GUI as follows:

We are going to rename database "Test" to "Test2".

Right click on database "Test" and select Tasks > Detach... and the following will open. Click OK to detach the database.

detach database using ssms

To reattach, right click on Databases and select Attach...

Then click the Add button and select the MDF file for the database you want to reattach.

Here we are reattaching database "Test", but we will attach as "Test2".

attach database using ssms

One thing to note is by changing the name of the database using any of these techniques you are only renaming the database. The physical files still have the same names, so if you want to also change the name of the files the best approach is to use Option 4. Before you reattach the files you need to first change the name of the physical files and then when you do the reattach you can specify the renamed files.

Important Note - Need Exclusive Database Access

In order to rename a database you will need to have exclusive access to the database, which means there are no other database connections using the database.

I will open another query window and USE database Test2, this way there is another connection to the Test2 database.

Then when I try to rename database "Test2" back to "Test", it fails since I did not have exclusive access and I got this error message.

rename error message
Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.

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

View all my tips


Article Last Updated: 2022-02-25

Comments For This Article




Thursday, January 24, 2019 - 4:05:09 PM - JimC Back To Top (78871)

Perfect. After trying different sites with their explanations, this covered it 100 percent.

Thank you

 

Tuesday, March 22, 2016 - 10:59:53 AM - Varney Fahnbulleh Back To Top (41029)

 THANKS ALOT FOR THIS LESSON

 

 

Monday, July 13, 2015 - 6:47:19 AM - Santosh Gunda Back To Top (38186)

 

Step 1:  Set the DB in Single User Mode.

 

IF DATABASEPROPERTYEX (N'ABC', N'Version') > 0

BEGIN

ALTER DATABASE [ABC] SET SINGLE_USER

WITH ROLLBACK IMMEDIATE;

END

GO

 

Step 2: Rename the DB

EXEC sp_renamedb 'ABC', 'CBA'

 

Step 3: Set the DB back in Multi User Mode.

 

IF DATABASEPROPERTYEX (N'CBA', N'Version') > 0

BEGIN

ALTER DATABASE [CBA] SET Multi_USER

WITH ROLLBACK IMMEDIATE;

END

GO

Sunday, May 18, 2014 - 11:27:21 AM - Raul A. De Jesus Back To Top (30821)

Thanks for this article, it save me a time after trying several different options within the same server.

Friday, January 17, 2014 - 3:18:50 PM - Greg Robidoux Back To Top (28128)

@Monideepa

In order to rename a database you have to have exclusive access, so you need to get everyone out of the database first.

Friday, January 17, 2014 - 2:28:53 PM - Monideepa Back To Top (28125)

Hi,

 

If I need to rename my DB while it is being accessed by other customers across the world can I use the same method? How can i rename the DB in this case.

Tuesday, October 8, 2013 - 5:18:26 AM - emem umoh Back To Top (27076)

Greg Robidoux Thank You very much!!!! short and precise. It actually got me out of a Fixed!!!!!

Tuesday, September 17, 2013 - 2:26:31 AM - Hemant Kapre Back To Top (26827)

 

Hi,

I executed below remane db command on sql server 2000 and it works succefully.

ALTER DATABASE oldName MODIFY NAME = newName

Friday, July 12, 2013 - 7:38:57 AM - madhu Back To Top (25811)

Sir, I have joined as a T-SQL Programmer in a startup company, at beginning stage. 

what is the diff btween SQL programming and T-SQL programming?

How to get succeed in the job?

Please help me

Is there any project, I can do practice in my leasure time

Please....

Monday, June 24, 2013 - 7:46:40 AM - Greg Robidoux Back To Top (25536)

@amit - you will need to remove mirroring, rename database and setup mirroring again.

Saturday, June 22, 2013 - 11:06:26 AM - amit Back To Top (25526)

please tell me the steps to rename a database that is configured with database mirroring its urgent please

Monday, April 29, 2013 - 6:10:27 AM - MattS Back To Top (23603)

Will any of these options also rename the file sitting beneath the DB? One of our problems in data migration is using a standard DB name for an extract which we want to re-use in each subsequent test conversion but retain the old DB for investigative purposes so we need to rename the old extract db, i.e. <db_name>_1 etc and then create a new extract db <db_name> etc

Friday, April 26, 2013 - 11:37:37 PM - Jeff Moden Back To Top (23589)

@SQList wrote:

One important thing that was missed is that objects in other databases, like functions, views, procedures, that refer to the old name of the database need to be updated as well. Toe make sure you dont miss them you can check using:

Oh, be careful now.  If you check in BOL, the datatype of the "Routine_Definition" column of INFORMATION_SCHEMA.Routines is only NVARCHAR(4000) and will truncate longer procs which means that you may miss a whole lot of code.  It would be better to use the "Definition" column of "sys.SQL_Modules" which has a datatype of NVARCHAR(MAX) which will allow up to a Billion characters.

It's also important to note that INFORMATION_SCHEMA.Routines in 2005 (haven't checked 2008 or 2012) will not check Views.  It only checks stored procedures and functions while sys.SQL_Modules also checks views.  Neither will check synonyms.

As a bit of a sidebar, while it's still a good idea to check to be sure, there's never been a good reason to use 3 or 4 part naming in any stored procedure, function, view, etc, since Synonyms came out in 2005.

Friday, March 22, 2013 - 5:56:58 AM - ThorgalMan Back To Top (22954)

It could also be a good thing to change teh logical name to before detaching the 'old' database.

ALTER DATABASE [OLD] MODIFY FILE (NAME=N'OLD', NEWNAME=N'NEW')
GO
ALTER DATABASE [OLD] MODIFY FILE (NAME=N'OLD_log', NEWNAME=N'NEW_log')
GO

 

Monday, February 25, 2013 - 9:27:58 AM - the sqlist Back To Top (22401)

One important thing that was missed is that objects in other databases, like functions, views, procedures, that refer to the old name of the database need to be updated as well. Toe make sure you dont miss them you can check using:

 

select 

routine_catalog,

routine_schema,

routine_name,

routine_type,

routine_definition

from 

information_schema.ROUTINES 

where 

routine_definition like '%oldDbName%'

Friday, February 22, 2013 - 11:36:01 AM - Alex G Back To Top (22358)

Greg,

I wanted to know your opinion on the following procedure of renaming the database:

1. Perform full database backup (on the database that you want to rename)

2. Delete database that needed to be renamed

3. Create new database with the desire new name

4. Restore database using .bak file created in step # 1 and rename .mdf and .ldf files with the same name as your new database

Are there any disadvantages using this approach VS options you have recommended?

 

Thanks in advance

 

Saturday, February 9, 2013 - 2:29:26 AM - Mohan Shyam Sharma Back To Top (22024)

Thank you so much for helpful query or command line !! 

Thursday, January 31, 2013 - 1:40:51 AM - raj Back To Top (21820)

thank u fr the valueable information

 

Wednesday, December 19, 2012 - 1:00:35 AM - pradeep Back To Top (21021)

Hi everyone..I am learning SQl server 2008..Can anyone please tell me the steps to rename a database that is configured with database mirroring.....

Tuesday, December 18, 2012 - 11:48:06 AM - shruthi Back To Top (21016)

thanks a lot!

Tuesday, November 20, 2012 - 2:56:33 PM - Greg Robidoux Back To Top (20437)

Russ thanks for the comments. 

You are totally right about having to have exclusive access to the database.  This tip doesn't hit upon that and should probably be updated.

Another option is to use an ALTER DATABASE statement to put the database into single user mode.

Thanks for the feedback.

Tuesday, November 20, 2012 - 2:37:55 PM - Russ Thomas Back To Top (20436)

It's probably worth including that using the SSMS gui (option 2) generates and issues the exact same statement behind the scenes as option 1.  You can verify this with SQL Profiler. 

Also, you need an exclusive lock on the entire database for option 1 & 2.  Something to be aware of when trying to rename a database that is currently in use.  If there are open connections (even in an SSMS window) then the rename will fail.  Obviously detaching solves this problem forecfully.

When needed, you can easily close all connections to a database by taking it offline before re-naming.  Also a very easy step in the SSMS gui.

Monday, November 5, 2012 - 7:46:07 AM - SENDHILRAJA Back To Top (20215)

Thanks

Thursday, May 31, 2012 - 12:18:51 AM - karthick Back To Top (17742)

Thanks a lot for this valuable Info.

Thursday, February 23, 2012 - 7:25:31 AM - teddo Solomon Back To Top (16141)

Its short precise and to the point sol. thank u.

Saturday, October 17, 2009 - 2:42:24 PM - admin Back To Top (4216)

Ray,

Thanks so much for the additional information!

Thank you,
The MSSQLTips Team














get free sql tips
agree to terms