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
I have now since corrected the default location to be
However I am unable to move a database that was created in the wrong location i.e. from
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