May 10, 2021 at 1:12 am
We had a power outage on one of our hosts, and it unfortunately caused some problems on two of our database servers. Fortunately this is a DEV environment - but we have some fairly large projects in development and so we needed to salvage whatever we could.
One server is our primary database server for applications; the other is a reporting server that contains some report oriented databases, as well as log shipped copies of the application databases from the primary. The primary server had a few databases go corrupt - fortunately we had backups. The reporting server had master go corrupt; but no other corruption as far as I could determine. I ended up recreating master from command line, since the backup drive was still down.
The issue I am having is on the reporting server. When I try to attach a database, I get an error stating that SQL cannot write to the MDF file, access denied. However, I am able to restore databases putting the files in the same folder with no issue. If I copy the MDF file to a different folder on the same drive, I can attach it without issue. I've reviewed the security on both folders and they are identical.
We do have transparent data encryption enabled on the server - however, that all seems to be working correctly. I can attach the databases once they're in the new folder, and I can log ship from primary... I don't see how that would make it matter where the file is physically located.
One thought was that perhaps there is still a file lock from SQL Server going down, but the server has been restarted multiple times since the incident - and also that would prevent (or at least throw a popup) when trying to move the files, so I doubt that is it.
At the end of the day, I don't really care that the files are in a new folder; but I'd like to understand the issue in case something like this happens in production.
May 10, 2021 at 2:41 am
If you are using the GUI, you need to run as admin.
Otherwise, script it out and it should work
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
May 10, 2021 at 5:30 pm
I would recommend running a chkdsk on it (and DBCC once you get it online). It could be that there was some corruption on the file/folder around permissions. So when copying to a new location, Windows re-applied the permissions at the destination resulting in proper permissions and everything looking good.
Windows can do weird things with unexpected power outages and I find doing a chkdsk and a DBCC CHECKDB is highly recommended as it can help address issues after unexpected outages so you can fix them before they become a long term problem. I've had folders where files in them cannot be deleted. You hit delete, they appear to be deleted, then you hit refresh and they pop back up. You check the recycle bin and they aren't in there even though you did hit delete. Delete from command prompt gives the same result. Deleting other files in that folder has no problems, but just 1 or 2 files in a folder fail to delete and there is no locks on them and no errors while deleting. Solution for me in that case was chkdsk.
With all unexpected outages on a server, I would do chkdsk to check for problems and do integrity checks on the SAN as well (if you have a SAN). Last thing you want is hardware failure. Note - doing this on a SAN MAY make the SAN inaccessible for a period of time. But I'd much rather have some downtime than have data corruption.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
May 12, 2021 at 5:53 am
The problem is due to the lack of permissions for SQL Server to access the mdf & ldf files. All these procedures will work :
YOu can check these references: https://www.mssqltips.com/sqlservertip/4542/access-is-denied-error-when-attaching-a-sql-server-database/
https://www.stellarinfo.com/blog/fix-sql-server-access-denied-error/
SQL Database Recovery Expert 🙂
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply