This Q&A was previously posted on the Stack Exchange Network of websites.
This content is licensed under the Creative Commmons Attribution-Sharealike (CC BY-SA) license. As the author of these answers, this archive serves as a canonical location for permalinks, which will outlive Stack Overflow. See all my answers by looking at the Stack Exchange post category.
After trying to restart moved database: Unable to open the physical file “D:\Data\MYDBNAME.MDF”
Question
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
asked 2016-03-29 by adolf garlic
Answer
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 FILEto 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 & 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.
answered 2016-03-29 by Andy Mallon