Error Opening .mdf's in MSSMS

Using Win7 Pro,  I've got several .mdf's that were originally created as part of an ASP.NET application in SQL Server Express 2005, under VS2005.  

I upgraded to VS2012, and to SQL Server 2012 EXPRESS.   The instance of SQL running on my machine now is  MSSQL11.SQLEXPRESS / 11.0.2218 RTM.  

During the upgrade, I did nothing to the the .mdf files.  

The application works fine under VS2012 and SQL 2012 Express;  there was very little code or configuration file change required.

I can open the .mdf's using the Server function in either VS2012 or VS2010 Shell, run queries, update the tables, modify/add tables, etc.

However, when I try to attach any of these .mdf's using MSSMS (this version)

Microsoft SQL Server Management Studio	11.0.2100.60
Microsoft Data Access Components (MDAC)	6.1.7601.17514
Microsoft MSXML						3.0 4.0 6.0
Microsoft Internet Explorer				9.10.9200.16686
Microsoft .NET Framework				4.0.30319.18052
Operating System						6.1.7601

I get "Error occurred while attaching.....  Click on hyperlink in message column"   but there's no hyperlink in the message column.

Any suggestions on what to do about this would be appreciated.

Thanks!
LVL 2
codequestAsked:
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.

Tapan PattanaikSenior EngineerCommented:
Hi codequest,

How to: Upgrade to LocalDB or Continue with SQL Server Express:

http://msdn.microsoft.com/en-us/library/ms165673%28v=sql.90%29.aspx

Upgrading SQL Server 2005 Express to 2008 R2 Express (for Backup Exec 2012) :

http://www.cosonok.com/2013/01/upgrading-sql-server-2005-express-to.html

Supported Version and Edition Upgrades:

http://technet.microsoft.com/en-us/library/ms143393.aspx

How to: Upgrade a SQL Server Express Report Server to Another SQL Server Edition:

http://technet.microsoft.com/en-US/library/ms403393%28v=SQL.90%29.aspx

http://technet.microsoft.com/en-US/library/ms403393%28v=SQL.90%29.aspx
0
codequestAuthor Commented:
@ tapanpattanaik

Thanks for the inputs.  Unfortunately I'm not able to translate those into an action plan.

Maybe I'm not understanding what's going on.  The information above is about upgrading servers.   However, I've already got SQL 2012 Express set up, and it seems to be able to access the .mdf's built in SQL 2005 Express.  

==> However, the issue is that I can't attach the .mdf's using MS SQL Server Management Studio 2012.    

Because I can attach the files SQL 2012 Express through Visual Studio 2012 and 2010, it doesn't seem like there's a problem with the .mdf's.  Is there?  

==> Do the .mdf's have to be converted in some way?
0
x-menIT super heroCommented:
try backup the databases on 2005, and restore on 2012
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Vadim RappCommented:
You definitely need to find out what is the actual error. http://social.msdn.microsoft.com/Forums/sqlserver/en-US/329a2791-715f-4a00-859a-77c80fc204ce/sql-server-2008-r2-an-error-occured-when-attaching-databases- means several possible causes for this problem, one is "access denied" for the file, another inconsistency between mdf and log file. To look into #1, you can run Process Monitor and see if there's any "access denied" when monitoring i/o access.

Look in the event log, perhaps the error is there. You can also look in the log of sql server you try to attach the mdf to.
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
codequestAuthor Commented:
Per guidance from vadminrapp1:

I looked at the error in the log:  "access denied".  The link in the vadminrapp1 post said check the file permissions.  Gave the .mdf files full permissions for all users (my personal machine), and the files opened up fine in MSSMS.

Thanks much!
0
Vadim RappCommented:
We suggest that you now remove full permissions for everyone from the mdf files. The required permissions (which is full permisions for the database engine's service account only) should have been set by sql server when you attached them.  More in the article "Securing Data and Log Files" .
0
codequestAuthor Commented:
Thanks for the input.   I mis-spoke.  I'm not adding "everyone".

I'm adding "Users (computername\Users).

After I hit "apply", and then attach the .mdf (which then works), the permissions shows not "Users (computername\Users)", but "MSSQL$SQLEXPRESS"

Is that the behavior you were referring to?
0
Vadim RappCommented:
Yes. If "users" is no longer there, it means that not only it added service account for itself (MSSQL$SQLEXPRESS), but also did what I suggested - deleted the permissions for others.
0
codequestAuthor Commented:
Thanks!
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.