In one of our previous blog post, we had covered how to rename objects in SQL Server. Today we will cover how we can rename the database in SQL Server. In order to rename the database, we need to first put it in a single user mode. We will start with creating a test database first. Connect to Query Analyzer or management studio and issue following command to create the database.
CREATE DATABASE MY_TEST
We have created a database in its simplest form. One data file and one log file will be created in default directory location. We can check it using sp_helpfile system stored procedure. Now using sp_renamedb stored procedure, we will rename the database. Since we have created the database just now, there is no need to put database in single user mode but we will still go ahead and perform the step to avoid any errors.
EXEC sp_dboption ‘MY_TEST’, ‘single user’, ‘TRUE’
EXEC sp_renamedb ‘MY_TEST’, ‘NEW_TEST’
EXEC sp_dboption ‘NEW_TEST’, ‘single user’, ‘FALSE’
Commands are self explanatory. In the first line, we are putting database in single user mode. In Second line we are renaming database to the new name. Since database name is already changed, in the third line we are using new database name to put it back in the multi user mode. We can even write small stored procedure, which can take two arguments, (old database name and new database name) and perform the same steps within the stored procedure. This works for both SQL Server 2000 and SQL Server 2005. We can check the information about new database by using sp_helpdb command.
But in SQL Server 2005, Microsoft issued a warning to deprecate sp_renamedb stored procedure from the future releases and suggested to use ALTER DATABASE command. Though it is not yet decided that in which release sp_renamedb stored procedure will be put to rest. Following is the ALTER DATABASE syntax to rename the database. As we have already changed the database name for our previous test, we will change the database back to its original name.
ALTER DATABASE NEW_TEST MODIFY NAME = MY_TEST
Above command will also rename the database to the name specified in the MODIFY NAME clause. To rename the database, one should have either sysadmin or dbcreator fixed server role privilege.