Rename database

Hi
How do I rename the database file name?
Lasse
Hi Lasse,
1. For database rename see below command in books online
sp_renameDB
2. To rename a Logical database File name see
ALTER database <dbname> MODIFY FILE (NAME = 'logical file name', NEWNAME = 'new logical file name')
Thanks
Hari
SQL Server MVP
____________________________________
Lasse Wrote:
Hi
How do I rename the database file name?
Lasse
Sent via SreeSharp NewsReader http://www.SreeSharp.com
|||IF you mean logical file name:
ALTER DATABASE dbname
MODIFY FILE (NAME = 'oldname', NEWNAME = 'newname')
"Lasse" wrote:

> Hi
> How do I rename the database file name?
> Lasse
>
>
|||To add to Jack's response, you can rename physical file names by detaching
the database using sp_detach_db, renaming the file(s) and then reattaching
with sp_attach_db.
For tempdb, you can alter physical file names using ALTER DATABASE. The
change will take affect when tempdb is recreated during the next SQL Server
restart.
Hope this helps.
Dan Guzman
SQL Server MVP
"Lasse" <lars@.kpwood.com.bls> wrote in message
news:%23YavT5jNFHA.3492@.TK2MSFTNGP09.phx.gbl...
> Hi
> How do I rename the database file name?
> Lasse
>
|||Hi Dan,
I'm curious why you recommend detaching and attaching a database to rename
files for a user database, but the refere to the ALTER DATABASE statement
for tempdb. Using the ALTER DATABASE statement to rename files is not
restricted to system database, but that's sort of what your message implies.
Am I missing something?
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%23GpziWkNFHA.2612@.TK2MSFTNGP10.phx.gbl...
> To add to Jack's response, you can rename physical file names by detaching
> the database using sp_detach_db, renaming the file(s) and then reattaching
> with sp_attach_db.
> For tempdb, you can alter physical file names using ALTER DATABASE. The
> change will take affect when tempdb is recreated during the next SQL
> Server restart.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Lasse" <lars@.kpwood.com.bls> wrote in message
> news:%23YavT5jNFHA.3492@.TK2MSFTNGP09.phx.gbl...
>
|||Hi, Gail.
AFAIK, renaming files with ALTER DATABASE...MODIFY FILE can only be done
with tempdb. This seems to be supported by the following from the SQL 2000
SP3 Books Online excerpt:
<Excerpt href="http://links.10026.com/?link=tsqlref.chm::/ts_aa-az_4e5h.htm">
FILENAME can be specified only for files in the tempdb database, and the new
name does not take effect until Microsoft SQL Server is restarted.
</Excerpt>
I'd be happy to learn the syntax to rename user database files using ALTER
DATABASE instead of detach/rename/attach. Then I can submit feedback for
the BOL doc :-)
Hope this helps.
Dan Guzman
SQL Server MVP
"Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
news:eegSzr%23NFHA.3668@.TK2MSFTNGP14.phx.gbl...
> Hi Dan,
> I'm curious why you recommend detaching and attaching a database to rename
> files for a user database, but the refere to the ALTER DATABASE statement
> for tempdb. Using the ALTER DATABASE statement to rename files is not
> restricted to system database, but that's sort of what your message
> implies. Am I missing something?
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:%23GpziWkNFHA.2612@.TK2MSFTNGP10.phx.gbl...
>
|||> I'd be happy to learn the syntax to rename user database files using ALTER DATABASE instead of
> detach/rename/attach. Then I can submit feedback for the BOL doc :-)
As I recall, renaming physical file name using ALTER DATABASE is introduced on SQL Server 2005
(except for tempdb as it is there today). Perhaps Gail has been working a bit too much on the 2005
doc's? ;-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
|||> As I recall, renaming physical file name using ALTER DATABASE is
> introduced on SQL Server 2005
Ahh, that probably explains the confusion. It's documented in the SQL 2005
BOL :-)
Hope this helps.
Dan Guzman
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23XZxjSDOFHA.3984@.TK2MSFTNGP12.phx.gbl...
> As I recall, renaming physical file name using ALTER DATABASE is
> introduced on SQL Server 2005 (except for tempdb as it is there today).
> Perhaps Gail has been working a bit too much on the 2005 doc's? ;-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
|||Yes, sorry about the confusion. I was looking at Yukon BOL and forgot this
was restricted in SQL Server 2000.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:eBDtvdFOFHA.580@.TK2MSFTNGP15.phx.gbl...
> Ahh, that probably explains the confusion. It's documented in the SQL
> 2005 BOL :-)
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:%23XZxjSDOFHA.3984@.TK2MSFTNGP12.phx.gbl...
>