Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips


























































   Got a SQL tip?
            We want to know!

How to rename a SQL Server database

MSSQLTips author Greg Robidoux By:   |   Read Comments (23)   |   Related Tips: 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: 10/28/2012


About the author
MSSQLTips author Greg Robidoux
Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Comments and Feedback:
Saturday, October 17, 2009 - 2:42:24 PM - admin Read The Tip

Ray,

Thanks so much for the additional information!

Thank you,
The MSSQLTips Team


Thursday, February 23, 2012 - 7:25:31 AM - teddo Solomon Read The Tip

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


Thursday, May 31, 2012 - 12:18:51 AM - karthick Read The Tip

Thanks a lot for this valuable Info.


Monday, November 05, 2012 - 7:46:07 AM - SENDHILRAJA Read The Tip

Thanks


Tuesday, November 20, 2012 - 2:37:55 PM - Russ Thomas Read The Tip

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.


Tuesday, November 20, 2012 - 2:56:33 PM - Greg Robidoux Read The Tip

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, December 18, 2012 - 11:48:06 AM - shruthi Read The Tip

thanks a lot!


Wednesday, December 19, 2012 - 1:00:35 AM - pradeep Read The Tip

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.....


Thursday, January 31, 2013 - 1:40:51 AM - raj Read The Tip

thank u fr the valueable information

 


Saturday, February 09, 2013 - 2:29:26 AM - Mohan Shyam Sharma Read The Tip

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


Friday, February 22, 2013 - 11:36:01 AM - Alex G Read The Tip

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

 


Monday, February 25, 2013 - 9:27:58 AM - the sqlist Read The Tip

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, March 22, 2013 - 5:56:58 AM - ThorgalMan Read The Tip

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

 


Friday, April 26, 2013 - 11:37:37 PM - Jeff Moden Read The Tip

@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.


Monday, April 29, 2013 - 6:10:27 AM - MattS Read The Tip

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


Saturday, June 22, 2013 - 11:06:26 AM - amit Read The Tip

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


Monday, June 24, 2013 - 7:46:40 AM - Greg Robidoux Read The Tip

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


Friday, July 12, 2013 - 7:38:57 AM - madhu Read The Tip

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....


Tuesday, September 17, 2013 - 2:26:31 AM - Hemant Kapre Read The Tip

 

Hi,

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

ALTER DATABASE oldName MODIFY NAME = newName


Tuesday, October 08, 2013 - 5:18:26 AM - emem umoh Read The Tip

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


Friday, January 17, 2014 - 2:28:53 PM - Monideepa Read The Tip

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.


Friday, January 17, 2014 - 3:18:50 PM - Greg Robidoux Read The Tip

@Monideepa

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


Sunday, May 18, 2014 - 11:27:21 AM - Raul A. De Jesus Read The Tip

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



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.