Tuesday, December 05, 2006

Renaming a SQL Server database

There are three ways to rename a database in SQL Server. Two among the three ways are very simple and straight forward and the last one is a bit complex. Lets see the all the three of renaming a database

Using the stored procedure sp_renamedb is the simplest way of to rename a database. To rename a database, open a query analyzer (SQL Server 2000) or SQL Server Management Studio (SQL Server 2005) application and connect to the database server. Then change the current database to master. After changing the current database to master, execute the command ‘EXEC sp_renamedb ‘old_database_name’, ‘new_database_name’.

If you don’t know how to execute command SQL Server Management Studio you can make use of graphical user interface to rename a database. In the object browser displayed in Management Studio select the database which you want to rename and right click. In the list of menu option provided, select ‘rename’ and type the required new name of the database.

The final and complex way of renaming a database is to detach and attach a database. Detaching and attaching of a database can be done using a set of SQL commands of graphical user interface. I’ll explain how to do this in my next article.