error while changin the file path of DB

Question

Post reply

error while changin the file path of DB

Joy Smith San

SSC-Insane

Points: 24877
More actions
February 5, 2009 at 2:42 am

#218238

Dear All,
When I execute the following query for changing the file path of a database file am getting error.
Use Master
Go
Alter database TEST_FILE
Modify file(name=TEST_FILE, filename='D:\test')
ERROR is
"MODIFY FILE failed. Do not specify physical name."
What could be the problem..? Pls help.
SwmSan

Viewing 7 posts - 1 through 6 (of 6 total)

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

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 1

Actualy I DONT WANT to change the filenames.

I want to change the location of my MDF & LDF files....

(in someother folder.....)

maechismo_8514 SSCoach Points: 17510 More actions · Answer 2

Use Master
Go
Alter database TEST_FILE
Modify file(name=TEST_FILE, filename='D:\test')

You should specify the full path:

Use Master

Go

Alter database TEST_FILE modify file(name='TEST_FILE', filename='D:\test.mdf')

Alter database TEST_FILE

modify file(name='TEST_FILE_log' filename='D:\test_log.mdf')

Once done check the changed path using:

SELECT * From sys.sysfiles

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 3

Hi,

I did it that way.. this time it worked and showed the new file path properly. But when I restarted the SQL server I got the following error.

"Database DB_NAME' cannot be opened due to inaccessible files or insufficient memory or disk space.

See the SQL Server errorlog for details. "

Why this error.?

maechismo_8514 SSCoach Points: 17510 More actions · Answer 4

"Database DB_NAME' cannot be opened due to inaccessible files or insufficient memory or disk space.
See the SQL Server errorlog for details. "

Is there enough space in D:\ drive then?

Did you see the error log for details then? You should be able to see the reason. Post the log file if you are not able to analyze it. We would be able to help you out further

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 5

There is enough space in D:\. I will check the log and revert back.By the way, am doing it in sql express...does it make any diff..?

Leo Leong SSCommitted Points: 1615 More actions · Answer 6

i am not sure whether this will help.

normally, i will do a full backup of the db and modify its path during restoration. 🙂

kinda stupid but it works all the time for me.