MS SQL Server - Renaming a Database

Author

Renaming a Database

Ryan Langton

2005-06-29, 9:23 am

Is there any easy way to rename a SQL Server database?
I tried exporting to a new database but it's not transferring everything.

Thanks,
Ryan

Jens Süßmeyer

2005-06-29, 9:23 am

What about sp_renamedb ? IF users are connected you should consider using
the

ALTER DATABASE <Nameofthedb> SET
SINGLE_USER with rollback immediate

sp_renamedb <Nameofthedb>,<NewNameofthedb>

ALTER DATABASE <DBNAME> Set MULTI_USER

--
HTH, Jens Suessmeyer.

---
http://www.sqlserver2005.de
---
"Ryan Langton" <langton@ci.manhattan.ks.us> schrieb im Newsbeitrag
news:%23LaOUWLfFHA.1480@TK2MSFTNGP10.phx.gbl...
> Is there any easy way to rename a SQL Server database?
> I tried exporting to a new database but it's not transferring everything.
>
> Thanks,
> Ryan
>

Kevin3NF

2005-06-29, 9:23 am

sp_renamedb
Changes the name of a database.

Syntax
sp_renamedb [ @dbname = ] 'old_name' ,
[ @newname = ] 'new_name'

Examples
This example changes the name of the accounting database to financial.

EXEC sp_renamedb 'accounting', 'financial'

--
Kevin Hill
President
3NF Consulting

www.3nf-inc.com/NewsGroups.htm

www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.

www.experts-exchange.com - experts compete for points to answer your
questions

"Ryan Langton" <langton@ci.manhattan.ks.us> wrote in message
news:%23LaOUWLfFHA.1480@TK2MSFTNGP10.phx.gbl...
> Is there any easy way to rename a SQL Server database?
> I tried exporting to a new database but it's not transferring everything.
>
> Thanks,
> Ryan
>

Tibor Karaszi

2005-06-29, 9:23 am

.... and as of SQL server 2000, you can also rename a database using ALTER DATABASE.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/

"Kevin3NF" < KHill@NopeIDontNeedN
oSPAM3NF-inc.com> wrote in message
news:etm1rbLfFHA.3124@TK2MSFTNGP12.phx.gbl...
> sp_renamedb
> Changes the name of a database.
>
> Syntax
> sp_renamedb [ @dbname = ] 'old_name' ,
> [ @newname = ] 'new_name'
>
>
> Examples
> This example changes the name of the accounting database to financial.
>
> EXEC sp_renamedb 'accounting', 'financial'
>
> --
> Kevin Hill
> President
> 3NF Consulting
>
> www.3nf-inc.com/NewsGroups.htm
>
> www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
>
> www.experts-exchange.com - experts compete for points to answer your questions
>
>
> "Ryan Langton" <langton@ci.manhattan.ks.us> wrote in message
> news:%23LaOUWLfFHA.1480@TK2MSFTNGP10.phx.gbl...
>
>

Hari Prasad

2005-06-29, 8:23 pm

Hi ,

SP_RENAMEDB is fine to rename a database. But as Tibor pointed out it is
always recommended to use the
ALTER DATABASE statement to rename a database in sql 2000.

How to remove connections
-----------------------------
Alter database pubs set single_user with rollback IMMEDIATE

Rename the database
-------------------
Alter database pubs modify name = pubs1

Set multi user
---------------

Alter database pubs1 set multi_user

Thanks
Hari
SQL Server MVP

"Jens Süßmeyer" < Jens@Remove_this_For
_Contacting.sqlserver2005.de> wrote in
message news:uv5EOZLfFHA.2472@TK2MSFTNGP15.phx.gbl...
> What about sp_renamedb ? IF users are connected you should consider using
> the
>
> ALTER DATABASE <Nameofthedb> SET
> SINGLE_USER with rollback immediate
>
> sp_renamedb <Nameofthedb>,<NewNameofthedb>
>
> ALTER DATABASE <DBNAME> Set MULTI_USER
>
>
>
> --
> HTH, Jens Suessmeyer.
>
> ---
> http://www.sqlserver2005.de
> ---
> "Ryan Langton" <langton@ci.manhattan.ks.us> schrieb im Newsbeitrag
> news:%23LaOUWLfFHA.1480@TK2MSFTNGP10.phx.gbl...
>
>

Sponsored Links