how to rename a database in SQL Server 2000?

[yehong]

Is there a way to rename databases in the SQL Server 2000?

 

[nigelrivett]

See sp_rename_db

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[yehong]

I am getting this error message when I am running following command in Query Analyzer.

exec sp_renamedb 'olddbname','newdbname'


Server: Msg 5030, Level 16, State 2, Line 1
The database could not be exclusively locked to perform the operation.

 

[nigelrivett]

You have to get rid of all the users in the database.
have a look at current activity in enterprise manager or

select spid from master..sysprocesses where dbid = db_id('olddbname')



======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[fluteplr]

Also make sure that you are not in the database yourself. If you are running this from query analyser, make sure that the database dropdown on the tool bar is set to some other database.

 

[ClaireHCM]

Instead of using sp_rename_db, Microsoft recommands to use ALTER DATABASE MODIFY NAME = new_dbname

 

[fluteplr]

Learn something new every day. And this one works even if that you have the query analyser window open to the database you are changing.