How to Change Owner of Database in Sql Server

How to rename a SQL Server database

By:   |   Updated: 2019-04-29   |   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 and 2017:

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 and 2017, 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.

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.

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 detach the database:

EXEC sp_detach_db 'Test', 'true'

The attach the database:

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.

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

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

Next Steps

• In addition to changing the names of the databases you also need to check to see if there are any references in your application code to the database name.  This can be either within SQL Server or outside of SQL Server.  By making this name change, nothing else is changed so you may need to do a lot of additional work to change a database name.
• See if there are databases that could use a name change and plan the change process to take advantage of a more meaningful database name.
• Refer to these related tips:
  • Best Practice for renaming a SQL Server Database
  • Steps to Rename a Subscriber Database for SQL Server Transactional Replication
  • Can a published SQL Server database be renamed

Related Articles

Popular Articles

About the author

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

View all my tips

Article Last Updated: 2019-04-29

How to Change Owner of Database in Sql Server

Source: https://www.mssqltips.com/sqlservertip/1122/how-to-rename-a-sql-server-database/

Share this post