Learn more about SQL Server tools

 
 

Tutorials          DBA          Dev          BI          Career          Categories          Events          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

How to rename a SQL Server database


By:   |   Read Comments (25)   |   Related Tips: 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | More > Database Administration

Problem

Sometimes there is a need to change the name of your 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.

Option 1 - Rename SQL Server Database using T-SQL

This command works for SQL Server 2005, 2008, 2008R2 and 2012:

ALTER DATABASE oldName MODIFY NAME = newName

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

EXEC sp_renamedb 'oldName', 'newName'

Option 2 - Rename SQL Database using SSMS

If you are using SQL Server Management Studio, right click on the database name and select the option "Rename". This did not exist in SQL Server 2000, but if you use Management Studio to manage SQL Server 2000 you can also take advantage of this option.

rename database using ssms

Option 3 - Rename SQL 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 SSMS or you can do this by using the following commands:

EXEC sp_detach_db 'oldName', 'true'

EXEC sp_attach_db @dbname = N'newName', @filename1 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf', @filename2 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs_log.ldf'

Here we are detaching database "Test"

detach database using ssms

Here we are reattaching database "Test", but before we finish we change the name of the database to "Test2".

attach database using ssms

One thing to note is by changing the name of the database using one 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 simplest approach is to use Option 3.  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.

Next Steps


Last Update:





About the author





More SQL Server Solutions




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



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

 THANKS ALOT FOR THIS LESSON

 

 


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

 

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

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

@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

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 08, 2013 - 5:18:26 AM - emem umoh Back To Top

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

 

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

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

@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

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

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

@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

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

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

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 09, 2013 - 2:29:26 AM - Mohan Shyam Sharma Back To Top

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


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

thank u fr the valueable information

 


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

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

thanks a lot!


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

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

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 05, 2012 - 7:46:07 AM - SENDHILRAJA Back To Top

Thanks


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

Thanks a lot for this valuable Info.


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

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


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

Ray,

Thanks so much for the additional information!

Thank you,
The MSSQLTips Team


Learn more about SQL Server tools