[SQL Server] Rename a SQL Server Database

Well, this may be obvious for SQL DBA’s, but after completing a data migration effort tonight, I needed to rename a database in SQL Server. Well, you cannot just right-click the database name in SQL Server and choose rename. Instead, you need to get all users out of the database and then open the database in single user mode. At that point, you can run a system stored proc to rename the database. Pretty simple – the second time :)

Here are the steps:

• Kick any connected users out of the database.
• Open database in single-user mode by opening Enterprise Manager and right-clicking the database name, and opening the Properties dialog. In the dialog, go to the Options tab, and select the checkbox labeled Restrict Access and then select the radio button labeled Read-Only.
• (Important) Close Enterprise Manager, to be sure SQL Server does not count that session as a connected user.
• Open Query Analyzer and run the system stored procedure named “sp_renamedb”. The proc takes parameters of the old name and new name. For example: “sp_renamedb MyOldDatabaseName MyNewDatabaseName”
• Close Query Analyzer
• Open Enterprise Manager and undo the Restrict Access setting made earlier. (Your database should now be listed under the new name).

That’s it!

Advertisement