How to rename a production SQL server database.

[Ad] need a break from coding?

Today I had to rename a production Microsoft SQL server database, and was surprised to find that you can't use enterprise manager to rename an existing database. You also need to put the database into single user mode, before issuing a rename statement. Below is a small script which uses the sp_dboption command to toggle the database in and out of single user mode, while issuing a sp_renamedb command to rename the database. You will need to ensure that no users are actively connected to the database, before running this script.

USE master
GO
EXEC sp_dboption 'database1', 'Single User', True
EXEC sp_renamedb 'database1', 'database2'
EXEC sp_dboption 'database2', 'Single User', False
GO

If you have found this article helpful or useful please consider linking to it, emailing it to friends, or share it with others using social sites like del.icio.us, Stumble Upon or Twitter.

About the Author
Paul Bradley is a VB.NET software developer living and working in Cumbria. He provides PHP & MySQL bespoke development services via his software development company, Carlisle Software Limited.
He has over 20 years programming experience.

How to rename a production SQL server database.

Other Popular Articles

Categories & Topics