0

I moved database files as follows:

Alter database xyz set offline;

-- I then physically moved the files

Alter database xyz
Modify file
(Name = xyz_logical_name, 
Filename = 'D:\data\xyz.mdf')

Alter database xyz 
Modify file
(Name = xyz_log, 
Filename = 'D:\log\xyz_log.ldf')

Alter database xyz set online;

The database came online successfully.

However, on rebooting the host server (for another reason), I found the databases had disappeared eventhough the physical files were still in place.

I then tried to attach them using SSMS but got an error :

CREATE FILE encountered operating system error 5(Access is denied) while attempting to open or create the physical file 'D:\data\xyz.mdf'. (MSS, Error:5123)

Both my user account and the SQL Server service account have full access on the D drive.

Am I missing something please?

Improve this question
7
  • 2
    Do you have the right permissions on the files themselves? If you have another database (which works) compare the permissions on files.
    – rois
    Commented Nov 3, 2022 at 18:27
  • When SQL Server restarted what was the log error for the database that didn't come online? I believe that error of your attempt from SSMS is different from the one you got after reboot itself.
    – Ronaldo
    Commented Nov 3, 2022 at 23:52
  • @rois, thank you for your response. Yes, we did try that, checked permissions on another db that works and gave all same permissions to the files of this problem database in their new location. we then tried rebooting the server again but the database didn't reappear.
    – PTL_SQL
    Commented Nov 4, 2022 at 1:14
  • @Ronaldo, I had a look but nothing reported for the database that didn't come online. Only the ones that came online were reported as being online. A query to list all databases in the instance also did not return the problem database. Thanks all the same for your response.
    – PTL_SQL
    Commented Nov 4, 2022 at 1:17
  • 1
    @Ronaldo my sincere apologies for the delay in updating this request. Thank you so much for your time and help. We found that the application deployment which deployed the databases was behaving weirdly due to the database files move. Part of what it was doing was changing permissions on the database files. That was why I was unable to re-attach the database. It's all fixed now, thank you again for your prompt help. The suggestions did help me, much appreciated.
    – PTL_SQL
    Commented Nov 18, 2022 at 11:46

1 Answer 1

Reset to default
2

I would assume this is a problem with the SQL Server service account permissions. Check the user running the service, and then check to see if that account has permissions on the directory/volume in question.

Improve this answer
5
  • thank you for your contribution. However, there are other database files in the same location which are working correctly, so I would have thought the service account has correct permissions on the folders? Moreover the service account has local admin rights on the server. Any more thoughts?
    – PTL_SQL
    Commented Nov 4, 2022 at 1:21
  • 1
    It's possible the files themselves lack oermissions for the service user, though this is unlikely if the service account is a local admin, it's still worth validating.
    – Patrick Hurst
    Commented Nov 4, 2022 at 14:03
  • Thank you so much for your time and help and my sincere apologies for not updating earlier. Yes, the problem was with the database files permissions which were being very strangely changed by the application. We finally got to the bottom of it and it's all fixed now. Thanks again
    – PTL_SQL
    Commented Nov 18, 2022 at 11:50
  • In my case as @PatrickHurst mentioned file permission was the issue, as I copied from somewhere else
    – abdul qayyum
    Commented Feb 16, 2023 at 6:58
  • You can add NT service\MSSQLSERVER or your server instance name for file permissions
    – abdul qayyum
    Commented Feb 16, 2023 at 7:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.