After trying to restart moved database: Unable to open the physical file "D:\Data\MYDBNAME.MDF" 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
While the database is offline, you need to copy the files to the new location. This is not something that SQL Server does automatically when you alter the file location. My preferred method is to use the following steps:


Set the database offline
Copy (don't move) the database files
After copy completes successfully, ALTER DATABASE...MODIFY FILE to change metadata for file locations
Set the database online
Confirm the database restarts successfully


If there's a problem bringin the database online, the files are still in the original location, so you can rollback.

Delete the old files


When performing the copy, you'll need to ensure that the permissions on the files are the same in the new location as in the old location. Otherwise, you'll run into different problems.

If you are using ROBOCOPY to move the files, you can use the /COPYALL flag to ensure that security &amp; file info is copied along with the file.

You can also manually verify the permissions, and verify that the folder permissions are inherited to all contents of the directory.