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

Answer 1

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 & 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.

Answer 2

After step one of taking the database offline, physically move the files using Windows explorer, command line, etc.

Once you have the databases located in the proper path, then execute the alter database commands with the new filenames. Lastly, set the database online.