So the default location for databases on our server is
c:\program files\microsoft sql server\mssql11.mssqlserver\mssql\DATA
but due to space limitations we instead use
d:\data
I have now since corrected the default location to be d:\data
However I am unable to move a database that was created in the wrong location i.e. from c:\...\data
to d:\data
Trying
ALTER DATABASE MYDATABASE set offline
ALTER DATABASE MYDATABASE
MODIFY FILE ( NAME = 'MYDATABASE', FILENAME = 'D:\Data\MYDATABASE.MDF')
ALTER DATABASE MYDATABASE
MODIFY FILE ( NAME = 'MYDATABASE_LOG', FILENAME = 'D:\Data\MYDATABASE_LOG.LDF')
ALTER DATABASE MYDATABASE SET ONLINE
fails with the following error message
Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file "D:\Data\MYDATABASE.MDF". Operating system error 2: "2 (The system cannot find the file specified.)". File activation failure. The physical file name "D:\Data\MYDATABASE_log.lDF" may be incorrect.Msg 5181, Level 16, State 5, Line 1
Could not restart database "MYDATABASE". Reverting to the previous status.Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
The server can definitely access the new location as all other databases in d:\data
work ok