January 27, 2011

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

Alter Database

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:

Rename Database

Rename Database

Rename Database

Rename Database

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


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


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

 
  Feedback:

comments

1 Response to "Rename a SQL Server database"
  1. Dattatrey Sindol said...
    January 28, 2011 at 10:53 PM

    Good Post Madhivanan.

    However, using these approaches the Logical & Physical names of the database files would still remain the same and will not be renamed.

    Here is a detailed step-by-step procedure to rename a SQL Server Database completly & consistently.

    Best Practice for renaming a SQL Server Database

    Regards,
    Datta

 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions