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.