SQL Server builds a new log file when you attach an MDF file without its LDF file. Builds need a writable database and a clean shutdown. Three ways restore a SQL database without a transaction log file. Free methods use the Attach dialog or the ATTACH_REBUILD_LOG statement. Both break the log backup chain and call for a full backup right after the attach. Recovery software saves the data of a damaged MDF file in a fresh database.
Your server crashed and TrainDB.mdf is the only file you could copy. Its LDF file is gone and SQL Server refuses to attach the database.
Search results repeat the Remove button and stop there. Few explain which databases accept a rebuilt log and which attach attempts fail.
This guide explains every route to restore a SQL Server database without a log file, including the backup step most guides omit.
Per the CREATE DATABASE reference ATTACH_REBUILD_LOG creates a fresh log file of 1 MB in the folder set as the default for logs.
Which Conditions Decide the Log Rebuild?
Three conditions decide whether SQL Server builds a new log:
- Clean shutdown. Microsoft states that ATTACH_REBUILD_LOG needs a clean shutdown of the database.
- Writable database. Microsoft limits the rebuild to databases that are not in READ_ONLY mode.
- Data files in place. All MDF and NDF files must exist.
Match your case to a row below.
| Situation | Method |
|---|---|
| Clean shutdown and a screen | #1 |
| Clean shutdown and a script | #2 |
| Crash copy or damaged MDF file | #3 |
| Database in READ_ONLY mode | Supply the log file |
3 Ways to Restore SQL Database Without Log File
Methods #1 and #2 cost nothing but need a clean MDF file. Method #3 handles damaged files.
| Feature | Attach Dialog | ATTACH_REBUILD_LOG | Recovery Software |
|---|---|---|---|
| Price | No fee | No fee | Scan free and export with a license |
| Best for | One database on a screen | Scripts and several databases | Damaged or crash copied MDF files |
| Needs | File access for the service account | A clean shutdown | Windows PC and a spare MDF copy |
| Main limit | Rebuild fails after a crash | Breaks the backup chain | Trial export limit |
Method #1: Remove the Missing LDF in the Attach Dialog (Free, Needs a Clean MDF)
Microsoft documents the dialog in its attach a database guide. Five actions restore TrainDB without its log file:
- Open the context menu of Databases in Object Explorer and choose Attach.
- Click Add and select TrainDB.mdf.
- Check the Message column of the database details grid, where the log row shows File not found.
- Select the LDF row and click Remove.
- Click OK and refresh Databases.
Per Microsoft one of three rights allows the attach. They are CREATE DATABASE and CREATE ANY DATABASE and ALTER ANY DATABASE. Access to the files in their folder must exist for the Database Engine service account.
Microsoft advises in its detach and attach overview that you retain detached log files until the database attaches without them. Copy the LDF file somewhere safe before you try anything else.
Method #2: Rebuild the Log With T-SQL (Free, Needs a Clean Shutdown)
Scripts suit servers without a screen and repeat attaches. Name the MDF file only.
USE master;
GO
CREATE DATABASE TrainDB
ON (FILENAME = N'D:\SQLData\TrainDB.mdf')
FOR ATTACH_REBUILD_LOG;
GOPer Microsoft the statement builds the new log file when one or more log files are missing. Plain FOR ATTACH rebuilds the log too when the database is writable and shut down cleanly and only one log file is unavailable. Check the new file afterwards.
SELECT name, type_desc, physical_name
FROM TrainDB.sys.database_files;Microsoft states that ATTACH_REBUILD_LOG uses existing log files when they are available. Rebuilds start only when a log file is missing.
Method #3: Rebuild From a Damaged MDF File With EmailDoctor (Free Scan, Paid Export)
Crash copies fail the clean shutdown check and the rebuild is refused. EmailDoctor SQL Database Recovery restores a database without a log file and scans the MDF duplicate without a log file and sends its objects into a fresh database with its own log.
Copy the MDF file to a spare folder first. Vendor notes say the trial export includes one table of 25 rows and five more objects.
Scan the MDF Duplicate
- Double click the recovery software icon on the PC.
- Click Open and select TrainDB.mdf from the spare folder.
- Select Quick Scan when the file was detached cleanly and Advance Scan when the server crashed.
- Select the SQL Server release or accept the auto detected one.
- Check the scan report. Open any table to see its rows.
Move the Objects Into SQL Server
- Press Export at the top of the window.
- Mark SQL Server Database as the export format.
- Type your Server Name and use Windows Authentication.
- Click the Create New Database option and name the copy TrainDB_Restored.
- Mark the objects you need in the tree.
- Choose With Schema & Data to move rows and structure and click Export.
- Wait for the Export Completed window and refresh Databases in SSMS.
What Resets After the Attach?
Three items reset when SQL Server attaches the files:
- Log backup chain. Per Microsoft ATTACH_REBUILD_LOG breaks the chain and a full backup must follow the attach.
- Security options. Per Microsoft attach sets TRUSTWORTHY and the Service Broker options to OFF and disables cross database ownership chaining.
- Server metadata. Per Microsoft logins and other server level items can need recreating on the instance.
Start the backup first.
BACKUP DATABASE TrainDB
TO DISK = N'E:\Backups\TrainDB_AfterAttach.bak'
WITH INIT, STATS = 10;Microsoft advises DBCC CHECKDB on a nonproduction server for databases from untrusted sources.
DBCC CHECKDB (N'TrainDB') WITH NO_INFOMSGS;Which Problems Stop the Attach?
Five problems stop most attach attempts:
| Problem | Reason | Fix |
|---|---|---|
| Attach dialog still lists the LDF row | The grid shows the old log path | Select the row and click Remove |
| Rebuild is refused | The database did not shut down cleanly | Use Method #3 on a duplicate |
| Log cannot be built | The database is in READ_ONLY mode | Supply the original log file |
| Access is denied on the MDF file | The service account lacks access to the folder | Grant that account access to the file |
| Version error appears | A newer SQL Server created the database | Attach it on a release equal to or newer than the creating one |
When every fix fails use Method #3 on a spare copy.
Frequently Asked Questions
Can I Restore a SQL Database Without the Log File?
Yes. Per Microsoft databases that closed cleanly get a new log file on attach. Crash copies need Method #3.
Does ATTACH_REBUILD_LOG Need a Clean Shutdown?
Yes. Per Microsoft the statement requires a clean shutdown and all MDF and NDF files.
Does SQL Server Create the New Log in the Default Folder?
Yes. Microsoft sets the new file at 1 MB and places it in the default log folder.
Does the Rebuild Break the Log Backup Chain?
Yes. Per Microsoft the operation breaks the chain and calls for a full backup immediately.
Can a READ_ONLY Database Get a New Log File?
No. Microsoft states that the rebuild needs an update of the primary file and READ_ONLY databases forbid it. Supply the original log file.
Does Plain Attach Rebuild the Log Too?
Yes. Per Microsoft FOR ATTACH rebuilds one unavailable log file of a writable database that closed cleanly.
Does the Attach Need Special Permission?
Yes. Microsoft names three rights and one of them is enough.
Does Attach Reset the TRUSTWORTHY Setting?
Yes. Per Microsoft attach sets TRUSTWORTHY to OFF for security.
Can Recovery Software Scan an MDF File Alone?
Yes. Recovery software scans the MDF duplicate alone and sends its objects into a fresh database with its own log.
Does the Free Trial Restrict the Export?
Yes. Vendor notes cap the trial at one table of 25 rows and five more objects. Buying the software ends the cap.
Author’s Verdict
Restores without the log file stop only when the MDF file did not shut down cleanly. Try the dialog first and the statement second.
Back up the database as soon as it is online because the rebuild breaks the chain. Recovery software handles crash copies that SQL Server refuses.
Retain every LDF file from now on. Never delete one to save space.
- SQL Server builds a new 1 MB log file for writable databases that shut down cleanly.
- Attach dialog and ATTACH_REBUILD_LOG both work without the LDF file.
- Rebuilds break the chain of log backups and call for a full backup right away.
- Attach resets TRUSTWORTHY to OFF and server level items can need recreating.
- Recovery software exports a damaged MDF file into a fresh database with its own log.
- Check every step on a duplicate first.
Next Read: Explore Different Types of Backups in SQL Server
Also Read: Fix the SQL Database Restore Failed Error | Restore a SQL Database From a BAK File
