Renaming Database Files

How can I go about safely renaming the data and log files for a
database?
Thanks.Hello ExcelMan,
I would go this way:
- Detach the database
- Rename the files
- Attach the database
Ekrem Önsoy
"ExcelMan" <sfarkas@.sjfcg.com> wrote in message
news:1188583811.122857.12230@.g4g2000hsf.googlegroups.com...
> How can I go about safely renaming the data and log files for a
> database?
> Thanks.
>|||On Aug 31, 11:31 am, Ekrem =D6nsoy <ek...@.btegitim.com> wrote:
> Hello ExcelMan,
> I would go this way:
> - Detach the database
> - Rename the files
> - Attach the database
> --
> Ekrem =D6nsoy
> "ExcelMan" <sfar...@.sjfcg.com> wrote in message
> news:1188583811.122857.12230@.g4g2000hsf.googlegroups.com...
>
> > How can I go about safely renaming the data and log files for a
> > database?
> > Thanks.- Hide quoted text -
> - Show quoted text -
That's what I tried and it does not work. The database will not
reattach if you change the names. When I changed them back to what I
started with it did reattach.
Any other ideas?|||Did you specify the new names when you executed sp_attach_db?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"ExcelMan" <sfarkas@.sjfcg.com> wrote in message
news:1188588512.215040.28540@.22g2000hsm.googlegroups.com...
On Aug 31, 11:31 am, Ekrem Önsoy <ek...@.btegitim.com> wrote:
> Hello ExcelMan,
> I would go this way:
> - Detach the database
> - Rename the files
> - Attach the database
> --
> Ekrem Önsoy
> "ExcelMan" <sfar...@.sjfcg.com> wrote in message
> news:1188583811.122857.12230@.g4g2000hsf.googlegroups.com...
>
> > How can I go about safely renaming the data and log files for a
> > database?
> > Thanks.- Hide quoted text -
> - Show quoted text -
That's what I tried and it does not work. The database will not
reattach if you change the names. When I changed them back to what I
started with it did reattach.
Any other ideas?|||There must be something you miss.
Tom could be right. How about trying this process via SSMS?
--
Ekrem Önsoy
"ExcelMan" <sfarkas@.sjfcg.com> wrote in message
news:1188588512.215040.28540@.22g2000hsm.googlegroups.com...
On Aug 31, 11:31 am, Ekrem Önsoy <ek...@.btegitim.com> wrote:
> Hello ExcelMan,
> I would go this way:
> - Detach the database
> - Rename the files
> - Attach the database
> --
> Ekrem Önsoy
> "ExcelMan" <sfar...@.sjfcg.com> wrote in message
> news:1188583811.122857.12230@.g4g2000hsf.googlegroups.com...
>
> > How can I go about safely renaming the data and log files for a
> > database?
> > Thanks.- Hide quoted text -
> - Show quoted text -
That's what I tried and it does not work. The database will not
reattach if you change the names. When I changed them back to what I
started with it did reattach.
Any other ideas?|||Logical or physical ones?
Whatever you try, first make a full backup.
See "alter database" statement in BOL. One way could be doing a backup and
restore it using options "replace" and "move".
create database test
go
select [name], physical_name
from sys.master_files
where database_id = db_id('test')
go
backup database test
to disk = 'c:\temp\test.bak'
go
alter database test
set single_user with rollback immediate
go
restore database test
from disk = 'c:\temp\test.bak'
with replace,
move 'test' to 'C:\Program Files\Microsoft SQL
Server\MSSQL.2\MSSQL\DATA\test1.mdf',
move 'test_log' to 'C:\Program Files\Microsoft SQL
Server\MSSQL.2\MSSQL\DATA\test1_log.ldf'
go
alter database test
modify file (name = 'test', newname = 'test1')
go
alter database test
modify file (name = 'test_log', newname = 'test1_log')
go
select [name], physical_name
from sys.master_files
where database_id = db_id('test')
go
drop database test
go
I played a little bit trying to change the physical file names usainf "alter
database" but there is a msg saying:
"The new path will be used the next time the database is started."
but I do not know how to start the db programatically. I tried setting it to
offline and then online but it fails. Here is the script.
use master
go
create database test
go
select [name], physical_name
from sys.master_files
where database_id = db_id('test')
go
alter database test
set single_user with rollback immediate
go
alter database test
modify file (name = 'test', newname = 'test1')
go
alter database test
modify file (name = 'test_log', newname = 'test1_log')
go
alter database test
modify file (name = 'test1', filename = 'C:\Program Files\Microsoft SQL
Server\MSSQL.2\MSSQL\DATA\test1.mdf')
go
alter database test
modify file (name = 'test1_log', filename = 'C:\Program Files\Microsoft SQL
Server\MSSQL.2\MSSQL\DATA\test1_log.mdf')
go
alter database test
set online
go
select [name], physical_name
from sys.master_files
where database_id = db_id('test')
go
alter database test
set multi_user
go
drop database test
go
AMB
"ExcelMan" wrote:
> How can I go about safely renaming the data and log files for a
> database?
> Thanks.
>