We help IT Professionals succeed at work.

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
Comment
Watch Question

Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

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

Author

Commented:
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.
Senior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
>> How have you been? <<

Eh, still alive I guess :-) .


You have two choices to make the change (assuming SQL 2005):
1) alter the file names, take the db offline, physically move the files, and bring the db back online
2) alter the file names, take SQL down, physically move the files, and bring SQL back up


I'll assume option 1)


USE [IRLRV3]
EXEC sp_helpfile
--IMPORTANT: copy/write down the logical file names, which will be the first column in the output

USE master

ALTER DATABASE [IRLRV3]
MODIFY FILE (
    NAME = <logical_data_name_from_sp_helpfile_cmd_above>,
    FILENAME = '<path_to_new_file_\_new_data_file_name>'
)

ALTER DATABASE [IRLRV3]
MODIFY FILE (
    NAME = <logical_log_name_from_sp_helpfile_cmd_above>,
    FILENAME = '<path_to_new_file_\_new_log_file_name>'
)

ALTER DATABASE  [IRLRV3]
SET OFFLINE

--physically move files by whatever method you prefer, for example using Windows explorer

ALTER DATABASE  [IRLRV3]
SET ONLINE

--verify db works and shows the new location

Author

Commented:
Scott,

Winner winner chicken dinner...

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

Thanks!
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

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

Author

Commented:
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!  :)