How to Recover a SQL Server Database from an MDF File Without the LDF

Every SQL Server database has at least two files: the MDF, which holds the tables, indexes and other objects, and the LDF, the transaction log. When the LDF is deleted, lost with a failed disk, left behind on another server or corrupted, SQL Server will usually refuse to attach the MDF on its own, even though the MDF still contains the data.

A missing log is a reason SQL Server will not open the database in the normal way, not a reason the data is gone. This guide explains why the attach fails, which built-in options are safe to try, which ones cost data, and how the tables are recovered from the MDF alone.

SQL Server writes every change to the transaction log first and to the data file later. At any moment the MDF can be missing changes that exist only in the log, or contain changes from transactions that were never committed. When the database starts, SQL Server uses the log to roll those changes forward or back so the data is consistent.

If the database was shut down cleanly, there is nothing left to roll forward or back, and SQL Server can build a new, empty log. If it was not (a crash, a power cut, a failed disk, a server that was simply switched off, or a ransomware attack), the old log is needed to make the MDF consistent, and SQL Server stops the attach rather than open a database that may be wrong.

That is the message most people see: 'File activation failure. The physical file name ... .ldf may be incorrect', followed by 'The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only', and finally error 1813, 'Could not open new database. CREATE DATABASE is aborted.'

Before trying anything, copy the MDF (and any NDF files) to a separate location and keep the originals untouched. Several of the methods below change the data file the moment they run, and some of those changes cannot be undone. With an untouched original, every option stays open, including professional recovery if the built-in methods fail.

If the LDF still exists but is damaged, copy it too and keep it with the MDF.

If the database was shut down cleanly, SQL Server can attach the MDF and create a new log: CREATE DATABASE YourDb ON (FILENAME = 'D:\Data\YourDb.mdf') FOR ATTACH_REBUILD_LOG. In SQL Server Management Studio the same thing happens when you attach the MDF and remove the missing log file from the list before clicking OK.

If this succeeds, run DBCC CHECKDB on the database straight away and make a full backup. If it fails with the 'log cannot be rebuilt' message, the database was not shut down cleanly and this method cannot work, however many times it is tried. The old sp_attach_single_file_db procedure does the same thing and fails in the same cases.

A log file from a different date, for example one restored from an old backup, is not a substitute. SQL Server reports that the files do not match (error 5173), and forcing them together produces a database that looks open but contains a mixture of two points in time.

The method most often posted online for a database that was not shut down cleanly is: create a new database with the same name and file layout, stop SQL Server, replace its MDF with the original, start SQL Server, put the database into EMERGENCY mode and run DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS, which builds a new log.

It sometimes produces a database that opens. The cost is in the name: the repair makes the database consistent by discarding whatever it cannot make consistent. Rows, pages and sometimes whole tables are removed, transactions that were half written stay half written, and SQL Server does not tell you in business terms what was lost. Once the repair has run on a file, the discarded data is gone from that file.

If you use this method at all, use it only on a copy, never on the original MDF, and compare record counts in the important tables before and after.

The MDF is made of 8 KB pages, and each data page carries rows of one table together with the information that identifies which table it belongs to. Recovery software reads these pages directly, without SQL Server attaching the database, rebuilds the table structure from the system tables inside the MDF, and writes every readable row out to a new, healthy database.

Because nothing is discarded to make SQL Server accept the file, this keeps far more data than an emergency repair: every row that is physically readable in the MDF comes back, also from files with damaged pages, a damaged header or a system catalog that was destroyed, and from partly encrypted files after a ransomware attack.

AS SQL Recovery & Repair Studio does this on your own computer. Its SQL Server tools first try the attach without the log file; if SQL Server refuses, the MDF is opened directly and its tables are recovered instead. The free edition shows the damage map and previews the recoverable tables and rows before you buy, and the original file is only ever read.

Do not run REPAIR_ALLOW_DATA_LOSS on the only copy of the MDF. Do not keep re-attaching the original file with different options: each attempt can change it. Do not restore an older backup over the same folder before the MDF has been copied. And if the log was lost because of a disk or RAID problem, do not keep working on that disk; copy or image it first.

Keep the LDF on reliable storage, not on a single old disk. Take regular full backups and, for databases in the full recovery model, log backups, so a lost log costs minutes of work instead of the database. Detach databases cleanly before moving them, and copy the MDF and LDF together. And test a restore now and then: a backup that has never been restored is a hope, not a backup.

We have solved many cases like this: databases whose log was deleted, lost with a failed disk or RAID, or encrypted by ransomware, and MDF files that SQL Server refused to attach at all. Send us the MDF, or tell us about the server, for a free assessment, and we will tell you which tables and how many records are recoverable before any work starts. No data, no charge.