If there is no open session using the database, you can change the name by pressing F2 while on the database.
If there is an open session on the database, you can kill these sessions with the help of the following script and use the F2 method again.
If you try to change the database name without killing these session, the below error will be occured.
Unable to rename xx.
Rename failed for Database ”.
The database could not be exclusively locked to perform the operation.
After running the below script, you must select and run the entire result set. But remember, by doing this process you will have destroyed all the sessions on the database.
|
1 |
select 'kill '+CAST(spid AS varchar(50)) from sys.sysprocesses where dbid=DB_ID('Test') |
The following script allows you to change the database name at once. However, this method also kills all open sessions in the database.
|
1 2 3 4 5 |
ALTER DATABASE Test SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO ALTER DATABASE Test Modify Name = TestNewName GO ALTER DATABASE TestNewName SET MULTI_USER WITH ROLLBACK IMMEDIATE |
Another method is to use the sp_renamedb stored procedure. You can change the database name with this method with the following script.
|
1 2 3 4 5 |
ALTER DATABASE Test SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO Exec sp_renamedb 'Test','TestNewName' GO ALTER DATABASE TestNewName SET MULTI_USER WITH ROLLBACK IMMEDIATE |
