How to rename a production SQL server database.

© copyright 20.May.2009 by Paul Bradley filed under Microsoft SQL Server


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.

Paul Bradley

About the Author
Paul Bradley is a VB.NET software developer living and working in Cumbria. He has over 20 years programming experience. He also produces e-learning videos at Linux by Example

Other Popular Articles

Categories & Topics

Home · Apache · JavaScript · Perl · PDF · PHP · MySQL · MSSQL · TAR · Ubuntu Linux · Video · Visual Basic

Browse the complete article history, and if you like what you see; consider subscribing to the rss feed.