Unable to attach mdf database to SQL Server using Studio Express

When I try to attach a database, receive the following error message.  My PC runs on Windows 7.  Thanks for any help you can provide.

An error occurred when attaching the database(s).

The details are:
TITLE: Microsoft SQL Server Management Studio Express
------------------------------

Attach database failed for Server 'MYNAME-HP\SQLEXPRESS'.  (Microsoft.SqlServer.Express.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)

------------------------------

Unable to open the physical file "C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data\My_DB.mdf". Operating system error 5: "5(Access is denied.)". (Microsoft SQL Server, Error: 5120)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.5000&EvtSrc=MSSQLServer&EvtID=5120&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------
BBRRGGAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anthony PerkinsCommented:
Does the file exist there?  If so, does the startup account for the SQL Server service have access to it?
0
BBRRGGAuthor Commented:
Yes, the mdf file is there.  I believe there should be no access issues.  Other mdf files in the same folder are attaching.

FYI
The last time I had this database open in Studio Express, I renamed the database with a " v2 " in the Object Explorer.  Now, when I go to attach it, I do not see the v2 in any of the mdf file names.
0
BBRRGGAuthor Commented:
FYI
The steps i'm taking are as follows:

Open SQL Server Mgmt Studio Express --> in Object Explorer, right-click on "Databases" --> Attach --> Add --> select my mdf file in the data folder --> OK --> it loads into the "databases to attach" window --> OK (this is when I get the error message above)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Anthony PerkinsCommented:
What happens when you do this:
EXEC master..xp_fileexist 'C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data\My_DB.mdf'
0
BBRRGGAuthor Commented:

File exists File is a Directory  Parent Directory Exists
1      0      1
0
Anthony PerkinsCommented:
I am afraid I am out of ideas.
0
Anthony PerkinsCommented:
On second thoughts, I suspect I know what may be going on.  The database is already attached.
0
Anthony PerkinsCommented:
You will also find that since the database is already attached you cannot move, rename or delete it.
0
BBRRGGAuthor Commented:
If its already attached, would it not show up in the object explorer?  I'm not seeing it listed in the object explorer unfortunately.
0
SilvarbullitCommented:
Does the account the SQL server is running as have modify (not just read) file system permissions on the .mdf file? NETWORK_SERVICE account perhaps?

Might also need modify file system permissions on the file if you are logged into SSMSE using a windows user account instead of a sql server login.
0
Ramesh Babu VavillaCommented:
right click on the server properties-> database setting-> the the path to where  your MDf file is present. remember it needs restart to effect the change in the path of SQL Server Database settings
0
Anthony PerkinsCommented:
>> I'm not seeing it listed in the object explorer unfortunately. <<
Have you refreshed the Databases collection in Object Explorer?  Or to be certain do you not see it listed in SELECT * FROM sys.master_files?
Failing that is this the only instance of SQL Server on this box?
Have you tried renaming the file?  If so what was the result?  If you can, you can always rename it back.
0
BBRRGGAuthor Commented:
I did refresh and still not seeing it.  When I run SELECT * FROM sys.master_files, I see the other databases but not the ones I've detached.  However, the path in the "Physical name" field is correct, leads to same 'DATA' folder that my desired mdf files are in.

FYI, I left all the default values in the checkboxes when I detached the (I believe 1 was checked and 3 were unchecked).

I believe this is the only instance of SQL server, is there a way I can confirm this?

I tried copying the mdf file within Windows Explorer and then renamed the copied file within Windows Explorer.  When I go to attach this renamed file, it shows up in the SQL Server attach window with the original database name.  And when I click OK to execute the attachment, I receive the same error message.

(changing point value to 500 points)


TITLE: Microsoft SQL Server Management Studio Express
------------------------------

Attach database failed for Server 'MYNAME-HP\SQLEXPRESS'.  (Microsoft.SqlServer.Express.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)

------------------------------

Unable to open the physical file "C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data\My_DB.mdf". Operating system error 5: "5(Access is denied.)". (Microsoft SQL Server, Error: 5120)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.5000&EvtSrc=MSSQLServer&EvtID=5120&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------
0
Anthony PerkinsCommented:
>> However, the path in the "Physical name" field is correct, leads to same 'DATA' folder that my desired mdf files are in.<<
Right.  That is the problem.  You are already using that data file for another database.


>>I tried copying the mdf file within Windows Explorer and then renamed the copied file within Windows Explorer. <<
That is not what I meant.  What I am saying is that if the data file is in fact used then you cannot rename it.

>>When I go to attach this renamed file, it shows up in the SQL Server attach window with the original database name.<<
Of course it will.  But there is nothing stopping you renaming the database and logical file names.
0
BBRRGGAuthor Commented:
Thanks for the feedback.

I appreciate your advice that another database is using this mdf file.  Can you please advise how I can determine which database is using this file, and how I can fix the situation (my goal is to attach the desired database to Studio Express?)  

Currently, the only database attached in Studio Express is the System Database (still getting same error.)
0
Anthony PerkinsCommented:
This query will show all the databases and all the files used by them:
SELECT      DB_NAME(database_id) DatabaseName,
      name LogicalFileName,
      CASE type
            WHEN 0 THEN 'Data File'
            ELSE 'Transaction Log File'
      END [DataFileType],
      physical_name
FROM      sys.master_files

If you do not see the specific data file listed in the physical_name column, than I am afraid I don't know what is happening.
0
BBRRGGAuthor Commented:
Thanks for the feedback.  The mdf file is not listed in the physical_name column for this query output.  To clarify, when I said above that the path in the "Physical name" field is correct, leading to the correct 'DATA' folder, I was just referring to the path to the folder, not to the mdf file itself.

It seems like it could be a permissions issue, but then again, I did at one point attach a database (though now, after I've detached it, I can no longer attach that same DB... getting the same error).

Can you think of any workarounds?  (ie. create a new DB and somehow copy over the tables?)
0
Anthony PerkinsCommented:
>>Can you think of any workarounds?<<
Unfortuantely, if you cannot attach the data files and you have no backups to restore, there is not a lot you can do about it to get the data.
0
Scott PletcherSenior DBACommented:
When you detach a db in SQL 2005 or later, the file permissions are changed.  This is a MS "security enhancement" that is a royal pain!!

You have to go into the file and change the file permissions -- and that is also tricky to make work -- or whatever id detached it, that specific id must re-attach it.

This is such a pain that I suggest not using detach and attach any more:
If the db is from a different instance, use backup and restore;
if on the same box, set db offline, make changes, set db back online.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BBRRGGAuthor Commented:
Thanks!
0
Jason clarkDBA FreelancerCommented:
Sometime files get corrupted due improper shutdown and hardware failure, so it is recommended that you should regularly check the hardware. After performing these discussed manual method fails then you should try the third party software to easily recover .mdf file.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.