How to Rename Database Files

Is there a way to totally rename a databases? I would like to change the
name of the database - like with sp_renamedb and the assosciated files so
that they still match. Is there an easy way to do this? I would really like
a TSQL script to do this if possible.
Thanks in advance.Hi Tim
What version are you using? Please always mention that right up front.
In SQL Server 2000 you can change the logical file names using ALTER
DATABASE, but in SQL Server 7 you cannot.
To change the physical files names, there is no way to do this in TSQL
(except for tempdb), so it would be tricky to do this in a script.
The steps to change physical file names are:
Detach the database
rename the files at the operating system
Attach the database, specifying the new file names
Check the docs for sp_attachdb for full details.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Tim Bird" <tsbdoulos@.nospambtopenworld.com> wrote in message
news:uDWhPZUKEHA.556@.TK2MSFTNGP10.phx.gbl...
> Is there a way to totally rename a databases? I would like to change the
> name of the database - like with sp_renamedb and the assosciated files so
> that they still match. Is there an easy way to do this? I would really
like
> a TSQL script to do this if possible.
> Thanks in advance.
>|||Tim
Detach the database. Rename the windows files and attach as whatever name yo
u like.
Regards
John|||Hi John
Note that this will not change the logical file names. That must be done
separately and in SQL Server 7 it is not possible to change the logical file
names at all.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"John Bandettini" <anonymous@.discussions.microsoft.com> wrote in message
news:851FB97A-324C-40DD-95D9-3704D66EB621@.microsoft.com...
> Tim
> Detach the database. Rename the windows files and attach as whatever name
you like.
> Regards
> John|||Hi,
In SQL 2000 the logical file name can be changed by using the below command.
The below step is not possible in SQL 7
ALTER DATABASE
MODIFY FILE
(NAME = logical_file_name,
NEWNAME = new_logical_name)
How to change the physical name in both SQL 2000 / SQL 7
1. detach the database using sp_detach_db <dbname>
2. Rename the physical files to new name
3. attach the database using sp_attach_db 'dbname','new physical MDF name
with path','new physical LDF name with path'
Thanks
Hari
MCDBA
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:e$uJwLVKEHA.2756@.TK2MSFTNGP10.phx.gbl...
> Hi John
> Note that this will not change the logical file names. That must be done
> separately and in SQL Server 7 it is not possible to change the logical
file
> names at all.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "John Bandettini" <anonymous@.discussions.microsoft.com> wrote in message
> news:851FB97A-324C-40DD-95D9-3704D66EB621@.microsoft.com...
name[vbcol=seagreen]
> you like.
>|||Yes, I just said that John's approach would not change the logical file
names. Earlier in the thread I told the original poster he could use ALTER
DATABASE to change the logical file names.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:urPajkVKEHA.3704@.TK2MSFTNGP11.phx.gbl...
> Hi,
> In SQL 2000 the logical file name can be changed by using the below
command.
> The below step is not possible in SQL 7
> ALTER DATABASE
> MODIFY FILE
> (NAME = logical_file_name,
> NEWNAME = new_logical_name)
> How to change the physical name in both SQL 2000 / SQL 7
> 1. detach the database using sp_detach_db <dbname>
> 2. Rename the physical files to new name
> 3. attach the database using sp_attach_db 'dbname','new physical MDF name
> with path','new physical LDF name with path'
> Thanks
> Hari
> MCDBA
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:e$uJwLVKEHA.2756@.TK2MSFTNGP10.phx.gbl...
> file
> name
>|||Kalen
Maybe I am missing something here, I did what I described and it worked just
fine. (This is in 2000, I did not have a 7 environment available.) I crea
ted a database called 'Fred'. I detached 'Fred'. I renamed the MDF and LDF f
iles to 'Pete'. I attached
database as 'Pete'. Database 'Pete' seems to be just fine. What did I miss?
Regards
John|||It depends. I understood the OP to say he wanted to change the file names to
match the new database name, and I assume he wanted the logical filenames to
match also. What good is having a database named Pete, with physical files
named Pete, but logical files named Fred?
I'm not surprised the db is working fine for you, but I think your logical
file names are still Fred. I understood the OP wanted all names changed. He
didn't just want it to 'work', he wanted it to 'look right' also.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"John Bandettini" <anonymous@.discussions.microsoft.com> wrote in message
news:0C49A43F-4B84-472B-88C0-29F561A56244@.microsoft.com...
> Kalen
> Maybe I am missing something here, I did what I described and it worked
just fine. (This is in 2000, I did not have a 7 environment available.) I
created a database called 'Fred'. I detached 'Fred'. I renamed the MDF and
LDF files to 'Pete'. I attached database as 'Pete'. Database 'Pete' seems to
be just fine. What did I miss?
> Regards
> John|||"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:e%23LFfB$KEHA.620@.TK2MSFTNGP10.phx.gbl...
> It depends. I understood the OP to say he wanted to change the file names
to
> match the new database name, and I assume he wanted the logical filenames
to
> match also. What good is having a database named Pete, with physical files
> named Pete, but logical files named Fred?
> I'm not surprised the db is working fine for you, but I think your logical
> file names are still Fred. I understood the OP wanted all names changed.
He
> didn't just want it to 'work', he wanted it to 'look right' also.
You are correct. That was my intention. Though it really had to be a program
based solution that an end user of our system could perform. In the end I
have gone for a slightly different solution. Unless anybody can think of a
way to get the SQL Server itself to rename files. As this is will be run
from a client machine and not the server.
Thanks for all your replies anyone who has helped.

> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "John Bandettini" <anonymous@.discussions.microsoft.com> wrote in message
> news:0C49A43F-4B84-472B-88C0-29F561A56244@.microsoft.com...
> just fine. (This is in 2000, I did not have a 7 environment available.)
I
> created a database called 'Fred'. I detached 'Fred'. I renamed the MDF and
> LDF files to 'Pete'. I attached database as 'Pete'. Database 'Pete' seems
to
> be just fine. What did I miss?
>|||Tim
Once the database is detached, SQL Server can rename the physical files
using xp_cmdshell. For an end-user to use this, it would have to be in a
stored procedure that you have granted the user permission to execute.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Tim Bird" <tsbdoulos@.nospambtopenworld.com> wrote in message
news:OBDshsCLEHA.1348@.TK2MSFTNGP12.phx.gbl...
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:e%23LFfB$KEHA.620@.TK2MSFTNGP10.phx.gbl...
names[vbcol=seagreen]
> to
filenames[vbcol=seagreen]
> to
files[vbcol=seagreen]
logical[vbcol=seagreen]
> He
> You are correct. That was my intention. Though it really had to be a
program
> based solution that an end user of our system could perform. In the end I
> have gone for a slightly different solution. Unless anybody can think of a
> way to get the SQL Server itself to rename files. As this is will be run
> from a client machine and not the server.
> Thanks for all your replies anyone who has helped.
>
worked[vbcol=seagreen]
> I
and[vbcol=seagreen]
seems[vbcol=seagreen]
> to
>