SQL Server Performance Forum – Threads Archive

renaming physical datafile ?

Hi All, Is it possible to rename a physical datafile(s) within a filegroup ? I want to implement some naming conventions. Thanks for your help and suggestions. bill

---

You can detach the database and reattach after they have been renamed, at the price of some downtime

---

,…. and for them refer to the SQL books online for SP_ATTACH_DB & SP_DETACH_DB topics. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

You can also use ALTER DATABASE statement. This is an example for tempdb. USE master
GO ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = ‘c:MSSQLdata empdb_NEW.mdf’)
GO ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = ‘c:MSSQLdata emplog_NEW.ldf’)
GO A server restart is needed. — Marek Grzenkowicz

---

Thanks guys.
Detaching and Reattaching worked perfectly. I tried the alter database statement. It gives the following error:
Server: Msg 5037, Level 16, State 1, Line 1
MODIFY FILE failed. Do not specify physical name.
I also figured out another way. Just take a backup of the db. drop the db.
Restore it from the backup, and specifiy the new physical file names there.
But, detaching and reattaching is much easier. thanks.
bill

---

True, you can use WITH MOVE option in RESTORE statement to accomplish and as you said DETACH and ATTACH is easy option with statements. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

I have no idea what the problem can be. I’ve just run it on my machine – there is no error, only a message: File ‘tempdev’ modified in sysaltfiles. Delete old file after restarting SQL Server.
File ‘templog’ modified in sysaltfiles. Delete old file after restarting SQL Server. — Marek Grzenkowicz

---

]]>