SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software SQL Server 2012 Download and T-SQL Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.

download SQL Server 2017
download SQL Server 2016
download SQL Server 2014

Attach database failed for Server

During attach database task, SQL Server administrators can get the following error message:
An error occurred when attaching the database(s).
Unable to open the physical file. Operating system error 2: "2(The system cannot find the file specified.)". (Microsoft SQL Server, Error: 5120)

The reason of this error is missing .ldf file (database log file) during SQL Server attach database task.

Let's try to simulate the error case with an example. I download AdventureWorks sample database for SQL Server 2012. Extract it and moved data file to SQL Server Data folder.

Now connect to SQL Server 2012 database instance using Microsoft SQL Server Management Studio. In Object Explorer window right click on the Databases node anc choose "Attach..." action from context menu.
In Databases to attach screen choose the database file for sample AdventureWorks database.

Please note that on the AdventureWorks2012 database details, in the log file details row you will realize the "Not Found" message. This is actually a clue for solving the problem that will occur in the next step for SQL Server 2012 database administrators.

After you click OK button to attach database (sample database for SQL Server 2012), SQL engine will throw the following error:

An error occurred when attaching the database(s). Click the hyperlink in the Message column for details.

Here is the details of the error that occured during database file attachment.

Attach database failed for Server 'servername'. (Microsoft.SqlServer.Smo)

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

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

Unable to open the physical file "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorks2012_Log.ldf". Operating system error 2: "2(The system cannot find the file specified.)". (Microsoft SQL Server, Error: 5120)

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

Now go back to the previous screen where the database to attach database file details are listed. You will remember, on the screen there is a warning for .ldf log file saying "Not Found" in message column. Highlight the missing log file row and press Remove button below as seen in below screenshot.

After you remove missing database file, continue database attach process as before. You will see the database attach process will be completed successfully.

SQL Server Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

SQL Server 2008 Blog

Certification Exams Blog

Reporting Services Blog

Analysis Services Blog

MS SQL Server Forums

Free Exam Vouchers

Copyright © 2004 - 2018 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems