Are you getting SQL server error 5120, when you try to attach some database files to Microsoft SQL server? This may happen because you have selected a file that is in use by other SQL server instance or selected a wrong file for the attachment. To fix this, firstly you have to check the location and status of SQL server files that you want to attach.
The default location of data & log files are:
C:Program-files\MSSQL\Data\Database-Name_Data1.mdf
C:Program-files\MSSQL\Data\Database-Name_Log1.ldf
Note: Your data & log files may have different location. You can change it according to your need.
The error message that you may got is look like:
Unable to open the physical file "C:Program-files\MSSQL\Data\Database-Name_Data1.mdf". Operating system error 5: "5(Access is denied.)". (Microsoft SQL Server, Error: 5120)
Possible Cause: As I have mentioned, this error message occurs due to many reasons but it may also occurs when you are not allowed to access the file directories. For the security purpose, database administrator does not allowed you to access the file directories. To access the file, you have to login as administrator or get permission to access the database files.
Resolution: As we know that solution is not rigid for sql database recovery. It may change according to the scenarios or causes. Here, we will discuss two scenarios and its possible solutions.
Solution 1: If you have found access permission problem with the database files then login as a administrator and fix the problem.
Solution 2: If you have found data file is present in C drive & log file is present in E drive then change the location of data or log and placed data and log files in a same drive. Now attach the database and fix it.
Point to Ponder: Generally, solution 1 is enough to solve Microsoft SQL error message 5129 because most of the database administrator (owner) does not allow every administrator to access the database files.
I don't think above solution will be appropriate for this problem.
ReplyDeleteIn Sql Server 2008/R2/2012, I found that every directory where SQL Server should access database must have "MSSQLSERVER" User Group permission with Full Access. But, I am still searching web for this solutions.
Because in my condition now problem is;
I am running Windows 8 Pro with MS SQL Server 2012 Express, but when proceed towards provided MSSQLSERVER user group full permission to my data directory, OS should no such user group or user available. But when I look into MSSQL installation DATA folder in OS Directory I am able to find there the same user group.