Attach Database Error

Question

I often faced this error that : TITLE: Microsoft SQL Server Management Studio ------------------------------ An error occurred when attaching the database(s). Click the hyperlink in the Message column for details. ------------------------------ BUTTONS: OK ------------------------------ I google it with many site but problem is not solved If someone faced this error before than let me know Database file is also created by SQL Server 2012 but still facing error while attaching

Jeyveen Bhoyroo · Answer

Hello Manav, To resolve this issue, make sure that you select the Map To Credential check box on the General tab of the Login Properties - sa dialog box before you change the "sa" login properties and then click OK to close the dialog box. Else in Microsoft SQL Server, Right click on SQL Server Management Studio Click on Run as administrator. I hope this helps you. Kind Regards, Jeyveen Bhoyroo.

Nitin Sontakke · Answer

@Manish Kumar, I understood why you asked that question. Saw what answer Manav have posted and took your line of investigation further and stated that that possibility is ruled out. Hope that is okay...we are all after resolving the issue so didn't mention all this in detail.

Amit Gupta · Answer

Hey Manav Sometimes this occurs when you don't have administrative privilege, try running SQL Server as an administrator. Also try this Goto to your SQL directory path which contains all the mdf and log files, if you had tried earlier, this also creates some files there, remove all the relevant files that points to the newly attached database. After deleting all temporary files, copy your .mdf files to the installation directory of SQL (from where you had deleted the files) Connect to SQL Server -> Click on the server -> Right Click -> Attach Database Click Browse to go through the location where the .mdf file exits After mdf file path has been added to the list, just make sure the user is the current owner Thats all you have to do This works for me Hope this helps

Suthish Nair · Answer

Might these things causing problem, you need to check before attach... Check the servers disk space (destination), have enough size or not to attach. SQL Server doesn't have permission to access file, check with your IT Team. Trying to attach an .mdf file, that has not been detached properly. File might be corrupted. Software not properly installed, version conflicts etc...

Amit Gupta · Answer

The error seems that the version of mdf file is higher and your current SQL version is lower. Is it like that? If yes, then you must have to upgrade your SQL Server

Nigel Fernandes · Answer

Install a lower version of SQL server and try attaching it to that.

Manav Pandya · Answer

@Bikesh Srivastava Thanks for replying sir Now my problem is not solved but i know that error is all about versioning I have old project , and trying to attach in sql server 2008 R2 and its not possible to attach lower or higher version of DB

Bikesh Srivastava · Answer

Hi manav, There are two way to attach database 1.Attach 2.restore firstly let me know what scenario you are using.

Daniel Jones · Answer

The message clearly says that the MDF file version is 706 and your server support version 611 and earlier. Which means, your database is created on SQL Server 2012, but it looks like you are trying to attach this database to SQL Server 2008 R2. Please verify your Server version

Manish Kumar · Answer

Actually, i thought db size is more then 10 GB and express edition does not support more then 10 gb of databases.

Nitin Sontakke · Answer

DB size doesn't seem to be an issue. If you see the hyperlink I stated, you will notice that the .mdf and .ldf created in SQL Server 2012 and you are trying to attach it to SQL Server 2008 R2, which is not possible. There two ways to go about it: 1/ Generate script in SQL Server 2012 - select compatibility version 2008 R2 and execute the script in 2008 R2. 2/ Install SQL Server 2012 and attach the db. Hope it helps!

Manish Kumar · Answer

I found your error on google and its telling run ssms as administrator. Try below link might be helpfull. http://schoening.it/blog/attach-database-failed-for-server-unable-to-open-the-physical-file-operating-system-error-5-5failed-to-retrieve-text-for-this-error-reason-15105-microsoft-sql-server-error/ Thanks

Manav Pandya · Answer

@Manish Hi sir my .mdf file size is 13 MB

Manav Pandya · Answer

@Nitin Thanks nitin sir , i will try this one

Nitin Sontakke · Answer

The link below explains the version number mentioned in the error and the SQL Server Version. http://sqlserverbuilds.blogspot.in/2014/01/sql-server-internal-database-versions.html Hope it helps!

Manish Kumar · Answer

Hi Manav, What is the size of your database. Thanks

Manav Pandya · Answer

@Amit No sir still not working But now error definition changed : TITLE: Microsoft SQL Server Management Studio ------------------------------ Attach database failed for Server 'MANAV-PC\SQLEXPRESS'. (Microsoft.SqlServer.Smo) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).100402-1540+)&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.ConnectionInfo) ------------------------------ The database 'CRUD_EX' cannot be opened because it is version 706. This server supports version 661 and earlier. A downgrade path is not supported. Could not open new database 'CRUD_EX'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 948) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=948&LinkId=20476 ------------------------------ BUTTONS: OK ------------------------------

Amit Gupta · Answer

Press win key -> type ssms -> right click -> run as administrator

Manav Pandya · Answer

@Nitin sir This is error that i got after clicking link :- TITLE: Microsoft SQL Server Management Studio ------------------------------ Attach database failed for Server 'MANAV-PC\SQLEXPRESS'. (Microsoft.SqlServer.Smo) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).100402-1540+)&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.ConnectionInfo) ------------------------------ Unable to open the physical file "D:\CRUD_EX_log.ldf". Operating system error 5: "5(failed to retrieve text for this error. Reason: 15105)". (Microsoft SQL Server, Error: 5120) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600&EvtSrc=MSSQLServer&EvtID=5120&LinkId=20476 ------------------------------ BUTTONS: OK ------------------------------

Manav Pandya · Answer

@AMit Gupta Thanks for reply I have tried all option from codeproject or stackoverflow But tell me how to run sql server as Administrator Thanks

Nitin Sontakke · Answer

It would be more helpful if you could post the error which is displayed after clicking the hyperlink as mentioned in the message above. This also happens when sql server process doesn't have permission on folder from where you are selecting .mdf and .ldf files.