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
indy500fanAsked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
>> 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
0
 
Scott PletcherSenior DBACommented:
NEWNAME is for the logical file name not the physical file name.  You need to use FILENAME = instead.
0
 
indy500fanAuthor 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.
0
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

 
indy500fanAuthor Commented:
Scott,

Winner winner chicken dinner...

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

Thanks!
0
 
Scott PletcherSenior DBACommented:
You're welcome!  Sorry it wasn't exactly what you wanted, but I think it's the best available.
0
 
indy500fanAuthor 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!  :)
0
All Courses

From novice to tech pro — start learning today.