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
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP

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 with an example case for database attachment. I download AdventureWorks sample database for SQL Server 2012. Extract sample database compressed file 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.

attach SQL Server database data file without log file

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

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

attach database data file to SQL Server error details

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.

remove missing log file for database attachment task

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

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019

Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.