Want to change logical file name.

Question

Post reply

Want to change logical file name.

DarylAndDaryl

Default port

Points: 1491
More actions
July 13, 2006 at 6:39 am

#61452

I am using SQL Server 2000 SP3 on Windows 2000 SP4.
I created my database by restoring a different database's backup overtop of my database. Now I would like to change the logical file names to be similar to my database name. According to BOL, I should be able to execute an ALTER DATABASE MODIFY FILE, which fails with file does not exist. Using Query Analyzer, I have tried executing this statement from within the master database and from within my database and I get the same results. I even executed "SELECT FILE_NAME(1)" to make sure I was spelling the logic file name correctly. I even tried putting quotes around the logic file names in the ALTER DATABASE MODIFY FILE statement. Does anyone have a solution? Below is my code:
SELECT FILE_NAME(1)
ALTER DATABASE CPM6_Test
MODIFY FILE (NAME = DDPM_CPM_DEV_dat, NEWNAME = CPM6_Test_Data)

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135375 More actions · Answer 1

you can modify the LOGICAL filename.

If you want to alter the physical filename , you're stuck with detatch / rename-file (e.g. with explorer) / attatch

from BOL :

FILENAME

Specifies an operating system file name. When used with MODIFY FILE, FILENAME can be specified only for files in the tempdb database. The new tempdb file name takes effect only after SQL Server is stopped and restarted.

testscript :

create database mydb

go

select * from mydb..sysfiles

go

use mydb

go

SELECT FILE_NAME(1)

go

alter database [mydb]

MODIFY FILE

(NAME = [mydb], newname=[mydb_datafile])

go

select * from mydb..sysfiles

go

use mydb

go

SELECT FILE_NAME(1)

go

-- use master

-- drop database mydb

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

DarylAndDaryl Default port Points: 1491 More actions · Answer 2

According to BOl, you can alter the logical file name and I just did it by putting my database in SINGLE USER mode first. Then I executed my ALTER DATABASE MODIFY FILE statement, which worked.

Johan Bijnens SSC Guru Points: 135375 More actions · Answer 3

I was probably updating my previous reply whilst you replied to it

It does not need to be in single user mode, but may have to wait for a system checkpoint to be able to publish it

I didn't encounter problems yet when modifying logical filenames