asked on

syntax for using the ALTER DATABASE MODIFY FILE help needed

Friends,

I am trying to use T-SQL to modify the file names of the mdf files and the ldf files. I think I can do it using the MODIFY FILE syntax.

This is what I have:

ALTER DATABASE [IRLRV3]
MODIFY FILE ( NAME = [C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\IRLRV2.mdf],
NEWNAME = [C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\IRLRV3.mdf] )

However, when I run it, I get the following error:

Msg 5041, Level 16, State 1, Line 1
MODIFY FILE failed. File 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\IRLRV2.mdf' does not exist.

Now, this file does exist, so something tells me, I'm not doing the syntax correctly.

Best Regards,
Eric

Scott Pletcher

NEWNAME is for the logical file name not the physical file name. You need to use FILENAME = instead.

indy500fan

ASKER

Scott,

How have you been?

Hey I tried that:

ALTER DATABASE [IRLRV3]
MODIFY FILE (
FILENAME = [C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\IRLRV2.mdf],
NEWNAME = [C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\IRLRV3.mdf] )

And I got the following message:

File option NAME is required in this CREATE/ALTER DATABASE statement.

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

indy500fan

ASKER

Scott,

Winner winner chicken dinner...

<Sigh>. That's not exactly the answer I was after, but you would be correct.

Thanks!

Scott Pletcher

You're welcome! Sorry it wasn't exactly what you wanted, but I think it's the best available.

indy500fan

ASKER

Yes, I was wanting a method to change the file names without taking the database/server offline, but that is really silly if you think about the processes logically.

THANKS AGAIN!

P.S. Glad you're still alive! :)