How to rename a SQL Server database

SolutionThere are a few ways of doing this.

Option 1

Use the following T-SQL: command to make the database name change.

EXEC sp_renamedb 'oldName', 'newName'

This command works for both SQL Server 2000 and SQL Server 2005, but this feature will not be supported in future versions of SQL Server. The new command that should be used for SQL Serer 2005 and beyond is:

ALTER DATABASE oldName MODIFY NAME = newName

Option 2

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

Option 3

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

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