Changing the logical name of a DB

Hi,
I recently restored one DB over another different one in order to import a
template of data into a new DB. I did this in Enterprise Manager. The gui
allowed me to change the physical file path & name to match the destination
file, but didn't allow me to change the logical name.
How can I now change the logical file names of the data & log files?
Many thanks for any help
You should do your best to learn the DDL equivalents of pointing and
clicking in the GUI, for two reasons: (a) you can easily store your scripts
in source control, and (b) you will have access to parts of the
functionality that didn't make it into the interface. Try this in Query
Analyzer:
ALTER DATABASE Database_Name
MODIFY FILE
(
NAME = Logical_Name,
NEWNAME = NEW_Logical_Name
);
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:8FFB9324-E3A9-4676-81C4-C8970539AC54@.microsoft.com...
> Hi,
> I recently restored one DB over another different one in order to import a
> template of data into a new DB. I did this in Enterprise Manager. The gui
> allowed me to change the physical file path & name to match the
> destination
> file, but didn't allow me to change the logical name.
> How can I now change the logical file names of the data & log files?
> Many thanks for any help
|||> How can I now change the logical file names of the data & log files?
You can use ALTER DATABASE...MODIFY FILE. For example:
ALTER DATABASE AdventureWorks
MODIFY FILE (
NAME='AdventureWorks_Data',
NEWNAME='AdventureWorks_Data_New')
See ALTER DATABASE in the Books Online for details.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:8FFB9324-E3A9-4676-81C4-C8970539AC54@.microsoft.com...
> Hi,
> I recently restored one DB over another different one in order to import a
> template of data into a new DB. I did this in Enterprise Manager. The gui
> allowed me to change the physical file path & name to match the
> destination
> file, but didn't allow me to change the logical name.
> How can I now change the logical file names of the data & log files?
> Many thanks for any help
|||Hello Dan,
Thank you very much for your help here.
Cheers
Ant
"Dan Guzman" wrote:

> You can use ALTER DATABASE...MODIFY FILE. For example:
> ALTER DATABASE AdventureWorks
> MODIFY FILE (
> NAME='AdventureWorks_Data',
> NEWNAME='AdventureWorks_Data_New')
> See ALTER DATABASE in the Books Online for details.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:8FFB9324-E3A9-4676-81C4-C8970539AC54@.microsoft.com...
>
|||What version are you running?
In SQL 2000 you cannot change the logical file names.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:8FFB9324-E3A9-4676-81C4-C8970539AC54@.microsoft.com...
> Hi,
> I recently restored one DB over another different one in order to import a
> template of data into a new DB. I did this in Enterprise Manager. The gui
> allowed me to change the physical file path & name to match the
> destination
> file, but didn't allow me to change the logical name.
> How can I now change the logical file names of the data & log files?
> Many thanks for any help
|||Hi Kalen,
Thanks for the reply but in fact it appears you can. I am using SQL 2000 &
have successfully changed the logical name simply by using the Alter table
modify file statement I was given above. It worked fine!
Thank you any way for your input on this though.
Cheer
Ant
"Kalen Delaney" wrote:

> What version are you running?
> In SQL 2000 you cannot change the logical file names.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:8FFB9324-E3A9-4676-81C4-C8970539AC54@.microsoft.com...
>
>
|||Hi, Kalen.

> What version are you running?
> In SQL 2000 you cannot change the logical file names.
Perhaps you are thinking of SQL 7. IIRC, the ability to change the logical
names was introduced in SQL 2000.
Hope this helps.
Dan Guzman
SQL Server MVP
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23dlcDj1UIHA.5980@.TK2MSFTNGP04.phx.gbl...
> What version are you running?
> In SQL 2000 you cannot change the logical file names.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:8FFB9324-E3A9-4676-81C4-C8970539AC54@.microsoft.com...
>
|||You're right... as Dan indicated it was actually SQL 7, when the new files
structures and ALTER DATABASE command were first introduced, that there was
no way to change the logical name.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:B2DD5503-5FC5-4B16-8B3B-C38655367FDF@.microsoft.com...[vbcol=seagreen]
> Hi Kalen,
> Thanks for the reply but in fact it appears you can. I am using SQL 2000 &
> have successfully changed the logical name simply by using the Alter table
> modify file statement I was given above. It worked fine!
> Thank you any way for your input on this though.
> Cheer
> Ant
> "Kalen Delaney" wrote:
|||Thanks, Dan.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:C6D04793-D874-4B42-A440-ED117325CE42@.microsoft.com...
> Hi, Kalen.
>
> Perhaps you are thinking of SQL 7. IIRC, the ability to change the
> logical names was introduced in SQL 2000.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:%23dlcDj1UIHA.5980@.TK2MSFTNGP04.phx.gbl...
>