How to Rename a SQL Server Database

Sometimes it is useful to rename a SQL Server 2000 Database.

Scenarios 

Typical scenarios include:

1. Moving a development database into production.
2. Redirecting the front-end of an application to a different version of the back-end.

The Approach 

You cannot rename a database within Enterprise Manager. Instead, you can execute a command in Query Analyzer.

You use sp_renamedb to rename the database:

EXEC sp_renamedb ‘<old name>’, ‘<new name>’ 

Restrictions

1. The command must be executed by a member of the ‘sysadmin’ fixed server role.
2. The database must be renamed in ‘single-user’ mode.
3. You must be in the master database to execute sp_renamedb.

Example Script

To rename ‘oldName’ to ‘newName’: 

USE master

GO

EXEC sp_dboption oldName, ‘Single User’, True

EXEC sp_renamedb oldName, newName

EXEC sp_dboption newName, ‘Single User’, True

Notes

1. You should ensure that there are no references to the old database name in the database that is renamed.
2. The names of the database files / filegroups will not be changed as a result of renaming the datbase.

Advertisement