Attaching Database Error

 

 

There is a very common error wend going to attach database in SQL Server 2008R2. It generally gives the error

"CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file... (Microsoft SQL Server, Error 5123)"

The problem is due to two different login do detach and attach.

So the files, when detached, were owned by the first login, but attach failed because the login that was used was not the owner of the mdf and ldf files.

We can detach the database by:

EXEC sp_detach_db mydb;

We can attach the database by:

CREATE DATABASE mydb

ON

(FILENAME='C:\MSSQL\mydb.mdf'),

(FILENAME='C:\MSSQL\mydb.ldf')

FOR ATTACH;

 

To solve this problem we have to give the full permission to both mdf and ldf file to attach login name by right clicking the file and selecting property and then security and then adding login name.

Hope you like that.

 

Posted by: MR. JOYDEEP DAS