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.
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:
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.
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.
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:
Here we are detaching database "Test"
Here we are reattaching database "Test", but before we finish we change the name of the database to "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 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.
- 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:
Last Update: 10/28/2012
About the author
View all my tips
- How to rename a SQL Server database...
- Best Practice for renaming a SQL Server Database...
- Steps to Rename a Subscriber Database for SQL Serv...
- Can a published SQL Server database be renamed...
- Steps to change the server name for a SQL Server m...
- Auto Rename SQL Server Tables and Their References...
- Script to rename constraints and indexes to confor...
- Renaming SQL Server database objects and changing ...
- More SQL Server DBA Tips...