Attach Database Without Transaction Log File

What if you’re moving a database to a new server by detaching and re-attaching database files and someone (not you of course) loses the log file? What if an old database needs to be brought online but the person coming to you only has an mdf file? Can you still attach the database in these scenarios? Let’s find out.

It’s Just Gone

We currently have our mdf and ldf files for ExampleDB.

To begin our test, let’s detach the database in SSMS:

USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'ExampleDB'
GO

Now, we can select our ExampleDB_log.ldf file in windows explorer and delete it. Only the mdf remains.

Let’s go back into SSMS, right click on the Databases folder in Object Explorer, and select “Attach…”:

We’ll click the Add button near the middle of the window and select our ExampleDB.mdf file:

Attach Here

Here is where things get interesting. In the lower half of the window we can see that ExampleDB_log.ldf has been added. We can adjust the column width to see the message provided:

Sounds simple enough. Let’s click OK and see SQL Server does what it says it will do:

Nope. Click OK to get back to our Attach window. Scroll to the right under “Databases to attach” and click Message to get more details:

Click OK to once again go back to the Attach window. Let’s look under the hood a bit. To find out what SQL Server is trying to execute, click “Script” and select Script Action to New Query Window:

That will take us to a window with the following script which SQL Server is using to attach the database. If we run the script, we get the same type of error as before:

USE [master]
GO
CREATE DATABASE [ExampleDB] ON
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\ExampleDB.mdf' ),
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\ExampleDB_log.ldf' )
FOR ATTACH
GO

Msg 5120, Level 16, State 101, Line 3
Unable to open the physical file “C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\ExampleDB_log.ldf”. Operating system error 2: “2(The system cannot find the file specified.)”.
Msg 1802, Level 16, State 7, Line 3
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

SQL Server is trying to help by adding that log file. What if we remove that line and just try to create with the mdf:

USE [master]
GO
CREATE DATABASE [ExampleDB] ON
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\ExampleDB.mdf' )
FOR ATTACH
GO

File activation failure. The physical file name “C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\ExampleDB_log.ldf” may be incorrect.
New log file ‘C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\ExampleDB_log.ldf’ was created.

The database attaches successfully and we’re back in business. Instead of changing the script, we could also accomplish this through the GUI by removing the ldf under database details and clicking OK:

Misleading Message?

SQL Server used to simply say “Not Found” instead of the “A new empty log file will be created” message. The process of removing the log file line and attaching worked then as well. I’m not sure yet whether or not the “A new empty log file will be created” message is misleading bug or if there’s another trick for that to work. Feel free to let me know if you’ve had a different outcome.

Thanks for reading!