To rename database it is very common to use for SQL Server 2000 user :
EXEC sp_renameDB 'oldDB','newDB'
sp_renameDB syntax will be deprecated in the future version of SQL Server. It is supported in SQL Server 2005 for backwards compatibility only. It is recommended to use ALTER DATABASE MODIFY NAME instead. New syntax of ALTER DATABASE MODIFY NAME is simple as well.
/* Create Test Database */
CREATEÂ DATABASE Test
GO
/* Rename the Database Test to NewTest */
ALTER DATABASE Test MODIFY NAME = NewTest
GO
/* Cleanup NewTest Database
Do not run following command if you want to use the database.
It is dropped here for sample database clean up. */
DROPÂ DATABASE NewTest
GO
Reference : Pinal Dave (https://blog.sqlauthority.com), BOL
30 Comments. Leave new
So, if you rename the old database with the new name and call drop on the test database, won’t that drop the old DB with the new name?
most of the time when i need any help i got here everything
and it is not good for me besides it is beneficial for all the DBA community.
you did great job………
how to add contraint in alter query
I renamed some databases since we need to standardize the naming convention. Unfortunately, it broke the SSIS package that used to be working. Somehow, it does not want to save the password even going directly to the configuration file. Any suggestion?
How about the physical name? How do you rename it?
when i am try to rename database it shows me following error msg
Msg 5030, Level 16, State 2, Line 1
The database could not be exclusively locked to perform the operation.
how do i resolve this problem?
Your Enterprise Manager Has been Open
Hi Sir
I was able to rename the database..but the logical name of the database didnot get renamed..is there any query to rename the database’s logical name as well
nice reply
Thank you so much Rich !!!! your seven stpes help me a great deal…
I am getting the same error as Eric:
Msg 5030, Level 16, State 2, Line 1
The database could not be exclusively locked to perform the operation.
How was this resolved?
Thanks in advance!
@Wendy,
If you want to change database name, then no user should be connected to database.
First keep the database in single user mode and then change the database name or disconnect all users from that database and then you should be able to rename database.
Regards
IM.
I have problem ALTER DATABASE
ALTER Database PersonnelMS SET Partner=’TCP://192.168.0.4:5022′
It shows message
Msg 1486, Level 14, State 2, Line 2
Database Mirroring Transport is disabled in the endpoint configuration.
Thanks, Done
Thanks for info, its working fine for me.
Neither method works for me. I’ve dug though loads of documentation trying to find the answer and nothing works. MS products are garbage.
Server: Msg 5030, Level 16, State 2, Line 1
The database could not be exclusively locked to perform the operation.
This Means?
Hi Saurabh,
First alter the database to single_user mode. This way all the connection would be broken and database would be available to be exclusively locked. Then again alter the database to multi_user mode and perform your operation.
Regards,
Pinal Dave
Hi Dave,
I have a situation where the db name is wrongly cased.
Say, “MyDataWarehouse” and would like to change to “MyDatawarehouse”. How can we do it?
SQL 2005. I tried renaming it in SQL Mgmt Studio and it did not work.
The other option is Either
– Take the db offline, drop the db and recreate the db and attach the files.
– Take complete backup, drop the db, recreate the db and restore it…
Cheers,
Sreedhar
Hi Sreedhar,
You can change the case with Alter Database Command as specified in start of the forum.
Ex.
ALTER DATABASE MyDataWarehouse MODIFY NAME = MyDatawarehouse
Regards,
Siddhesh
Dear Pinaldave,
1) Is anything like database alias exists there in SQL Server 2005? i.e. a feature similar to table alias.
2) Is there any way of overcoming the nvarchar(max) size which is 4000 chars. So that a longer, infact a very long, sql string can be passed to SP_ExecuteSQL procedure?
Actually, I need to run my crystal reports’ SP’s on different databases (Currrent and Historic/Archive DB) based on the reporting date passed as prameter to SP.
I need your expert opnion on this and if possible a Way to acheive this.
Regards,
Imran
PS: Table names in the both the current and history databases are the same i.e. same schema.
this command run succesfulyy thnaks to all member who provide 11
when i am renaming the database
using this query.
ALTER DATABASE KYRPLUS
Modify Name = KYRPLUS2june ;
the error occurred:-
Msg 5030, Level 16, State 2, Line 1
The database could not be exclusively locked to perform the operation.
Hi shilpa.
As Pinal Dave wrote in some reply on January 1, 2010 at 5:43 pm, the steps should be:
use master
go
/*
First alter the database to single_user mode.
This way all the connection would be broken and database would be available to be exclusively locked.
*/
ALTER DATABASE KYRPLUS SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
/*
Then again alter the database to multi_user mode and perform your operation.
*/
ALTER DATABASE KYRPLUS SET MULTI_USER WITH ROLLBACK IMMEDIATE
GO
/*
perform your operations. In this case, modify the name of the database
from: KYRPLUS
to: KYRPLUS2june
*/
ALTER DATABASE KYRPLUS
MODIFY name = KYRPLUS2june
GO