As DBAs we often have to rename databases for variety of reasons. In my organization I often have to restore the current database from Production to the development server and have to rename the old database in the Developement server. So today I wanted to take out time to write an article about renaming the databases in SQL Server.
Points to note while renaming the databases:
- Users with SA and dbcreator fixed server role can execute the sp_renamedb command.
- When we rename a database by sp_renamedb command, then the logical and physical file names and filegroups are not changed. If needed we need to explicitly change the filenames (which is always suggested in the Production Environment).
Today here I am only going to show how to change the name of the database by sp_renamedb command. Later in another blog I will show how to change the logical and physical filenames.
EXEC sp_dboption AdventureWorks, ‘Single User’, True
EXEC sp_renamedb ‘AdventureWorks’,‘AdventureWorks_NewDB’
EXEC sp_dboption AdventureWorks_NewDB, ‘Single User’, False