Friday, March 30, 2012

How to rename a database

As caption, how to rename a database?sp_renamedb
It is documented in SQL Book Online :- tsqlref.chm::/ts_sp_ra-rz_3yle.htm
HTH. Ryan
"kei" <kei@.discussions.microsoft.com> wrote in message
news:65DE5F9E-A2DC-44B6-B3BC-06A0EDAFF7FC@.microsoft.com...
> As caption, how to rename a database?|||Also, as of SQL Server 2000, it is recommended to rename a database using AL
TER DATABASE (although
sp_renamedb still exists and is supported).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:%23kAEKBBHGHA.1288@.TK2MSFTNGP09.phx.gbl...
> sp_renamedb
> It is documented in SQL Book Online :- tsqlref.chm::/ts_sp_ra-rz_3yle.htm
> --
> HTH. Ryan
> "kei" <kei@.discussions.microsoft.com> wrote in message
> news:65DE5F9E-A2DC-44B6-B3BC-06A0EDAFF7FC@.microsoft.com...
>|||sp_helptext 'sp_renamedb', will give you clear picture of what sp_rename DB
does.
Its "Alter Database" itself whats hapenning inside sp_renamedb
Thanks,
Sree
"Tibor Karaszi" wrote:

> Also, as of SQL Server 2000, it is recommended to rename a database using
ALTER DATABASE (although
> sp_renamedb still exists and is supported).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> news:%23kAEKBBHGHA.1288@.TK2MSFTNGP09.phx.gbl...
>|||> Its "Alter Database" itself whats hapenning inside sp_renamedb
It's true that sp_renamedb is basically just a wrapper for ALTER DATABASE so
you might as well eliminate the middle man and execute the command directly.
sp_renamedb will be removed in a future version of SQL Server.
Hope this helps.
Dan Guzman
SQL Server MVP
"Sreejith G" <SreejithG@.discussions.microsoft.com> wrote in message
news:16C5441C-5988-4B95-AD97-A05184B4964F@.microsoft.com...[vbcol=seagreen]
> sp_helptext 'sp_renamedb', will give you clear picture of what sp_rename
> DB
> does.
> Its "Alter Database" itself whats hapenning inside sp_renamedb
> Thanks,
> Sree
> "Tibor Karaszi" wrote:
>|||I agree with what you're saying, however i was surprised to see that BOL
suggests the opposite..
BOL - ALTER DATABASE
Remarks
To remove a database, use DROP DATABASE. To rename a database, use
sp_renamedb. For more information about decreasing the size of a database,
see DBCC SHRINKDATABASE.
tsqlref.chm::/ts_aa-az_4e5h.htm
HTH. Ryan
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OTlRGkCHGHA.3728@.tk2msftngp13.phx.gbl...
> It's true that sp_renamedb is basically just a wrapper for ALTER DATABASE
> so you might as well eliminate the middle man and execute the command
> directly. sp_renamedb will be removed in a future version of SQL Server.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Sreejith G" <SreejithG@.discussions.microsoft.com> wrote in message
> news:16C5441C-5988-4B95-AD97-A05184B4964F@.microsoft.com...
>|||>I agree with what you're saying, however i was surprised to see that BOL
>suggests the opposite..
It appears you are using the original RTM version of the SQL 2000 BOL rather
than the latest update. You can download the latest reference from
http://www.microsoft.com/downloads/...DisplayLang=en.
Here's what the latest SQL Server BOL says:
<Excerpt BOL=SQL2000" href="http://links.10026.com/?link=tsqlref.chm::/ts_aa-az_4e5h.htm">
To remove a database, use DROP DATABASE. To rename a database, use the
MODIFY NAME = new_dbname option with ALTER DATABASE. For more information
about decreasing the size of a database, see DBCC SHRINKDATABASE.
</Excerpt>
Also, from the SQL Server 2005 BOL.
<Excerpt BOL=SQL2005"
href="ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/7dd9d4ff-20e1-4857
-9a8e-a5bff767cf76.htm">
Important:
This feature will be removed in a future version of Microsoft SQL Server.
Avoid using this feature in new development work, and plan to modify
applications that currently use this feature. Use ALTER DATABASE MODIFY NAME
instead. For more information, see ALTER DATABASE (Transact-SQL).
</Excerpt>
Even though you can still use sp_renamedb in SQL 2000 and SQL 2005, I think
it's best to break old habits.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:OLGYbqCHGHA.3936@.TK2MSFTNGP12.phx.gbl...
>I agree with what you're saying, however i was surprised to see that BOL
>suggests the opposite..
> BOL - ALTER DATABASE
> Remarks
> To remove a database, use DROP DATABASE. To rename a database, use
> sp_renamedb. For more information about decreasing the size of a database,
> see DBCC SHRINKDATABASE.
>
> tsqlref.chm::/ts_aa-az_4e5h.htm
>
> --
> HTH. Ryan
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:OTlRGkCHGHA.3728@.tk2msftngp13.phx.gbl...
>|||You're correct... I'm on a loaner PC today, i assumed it had the BOL
update - it doesn't...
Thanks
HTH. Ryan
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:ek6mwNDHGHA.208@.tk2msftngp13.phx.gbl...
> It appears you are using the original RTM version of the SQL 2000 BOL
> rather than the latest update. You can download the latest reference from
> http://www.microsoft.com/downloads/...DisplayLang=en.
> Here's what the latest SQL Server BOL says:
> <Excerpt BOL=SQL2000" href="http://links.10026.com/?link=tsqlref.chm::/ts_aa-az_4e5h.htm">
> To remove a database, use DROP DATABASE. To rename a database, use the
> MODIFY NAME = new_dbname option with ALTER DATABASE. For more information
> about decreasing the size of a database, see DBCC SHRINKDATABASE.
> </Excerpt>
> Also, from the SQL Server 2005 BOL.
> <Excerpt BOL=SQL2005"
> href="http://links.10026.com/?link=ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/7dd9d4ff-20e1-48
57-9a8e-a5bff767cf76.htm">
> Important:
> This feature will be removed in a future version of Microsoft SQL Server.
> Avoid using this feature in new development work, and plan to modify
> applications that currently use this feature. Use ALTER DATABASE MODIFY
> NAME instead. For more information, see ALTER DATABASE (Transact-SQL).
> </Excerpt>
> Even though you can still use sp_renamedb in SQL 2000 and SQL 2005, I
> think it's best to break old habits.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> news:OLGYbqCHGHA.3936@.TK2MSFTNGP12.phx.gbl...
>

No comments:

Post a Comment