Wednesday, April 25, 2007

Renaming database

To rename the database, typically we have to take the database offline. But when you try to do that typically connection held to those database does not let you rename the database. So we need to take the database to Single User mode so that particular database accepts only one database connection. Dont try it take it "Single user" mode from Enterprise manager - then Enterprise manager itself will eat up the available connection and you will start sweating (If you are doing it live thinking these steps can be done quickly :)).

Go by TSQL way,

USE master GO
EXEC sp_dboption DevelopmentDB, 'Single User', True
EXEC sp_renamedb 'DevelopmentDB', 'ProductionDB'
EXEC sp_dboption ProductionDB, 'Single User', False