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
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
excellent website for sql
Hello Pinal,
Is there any logs so we can track DB name change
Hi Pinal,
1) if MODEL and MSDB databases are in suspect mode will my SQL server be alive/comeup?
2) if i have a table of 1TB, and fragmentation is 85%,apart from rebuild index,what other options do we have?
Hi Dave,
I want to rename a database that is in mirroring. Kindly share the steps for that.