November 28, 2016 at 1:42 pm
I am trying to move a database file to another drive. For some reason I keep getting invalid file.
Why the file name has a period in it I dont' know, but it seems to be confusing the statement.
I take the DB offline.
Move the file to the new location.
USE master;
ALTER DATABASE foo
MODIFY FILE (name=[DB_Data1.mdf] ,filename='D:\NewDBFile\DB_Data1.mdf');
I try to run the above statement and get invalid file name, but not sure what part it doesn't like since the file name and the actual file are the same.
What am I missing?
November 28, 2016 at 1:48 pm
Hmm, not sure. That file name should be allowed.
Verify again that the filename is correct:
USE <your_db_name>
EXEC sp_helpfile
The first column of the output will contain the logical file names.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 28, 2016 at 1:53 pm
My suspicion is it's having a problem with the "NAME=" in your alter.
The filename (on disk) is fine.
What you need is the "logical name" of the file for the NAME=
Run this to check the information:
use [foo];
go
select name
, physical_name
from sys.database_files;
Obviously, replace "foo" with whatever the name of the database in question is.
November 28, 2016 at 2:09 pm
Ok well this is crazy, but I had been doing a query for the logical name and filename. They all were named after the DB, but apparently the previous DBA had the logical and physical names for DB A swapped with an offline DB B.
DB A --> BDat.mdf D:\BDat.mdf
DB B --> ADat.mdf D:\ADat.mdf
So I would take A offline, B already was but when trying to swap the file on A it was actually B that was in the wrong place which is why it didn't work.
If that makes sense.
In any case the
USE FOO
EXEC sp_helpfile
returned the other DB name and I realized what was going on.
Thanks!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply