Rename a SQL Server database

Sometimes we may need to rename a SQL Server database for testing purposes or when a testing database goes online, we may need to rename it on a production database.

There are two methods to do this. Let us first create a sample database

CREATE DATABASE testing

Method 1: Use sp_renamedb procedure

EXEC sp_renamedb 'testing','development'

The procedure sp_renamedb will rename the database from ‘testing’ to ‘development’

However this procedure may not be available in future release of SQL Server. So instead make use of the ALTER database command

ALTER DATABASE testing
MODIFY name=development

Method 2: Use SQL Server Management Studio

Right click on database and select rename and type the new name and hit Enter. The database will be renamed. The steps are shown below:

Best practices when renaming a database

1. Do not rename a production database without taking a backup
2. Make sure the database is not accessed by other applications
3. Do not use special characters as part of the database name

About The Author

Madhivanan,an MSc computer Science graduate from Chennai-India, works as a works as a Lead Subject Matter Expert at a company that simplifies BIG data. He started his career as a developer working with Visual Basic 6.0, SQL Server 2000 and Crystal Report 8. As years went by, he started working more on writing queries in SQL Server. He now has good level of knowledge in SQLServer, Oracle, MySQL and PostgreSQL as well. He is also one of the leading posters at www.sqlteam.com and a moderator at www.sql-server-performance.com. His T-sql blog is at http://beyondrelational.com/blogs/madhivanan