How to Attach a Database Without Log File in SQL Server

To attach a database without log file, run CREATE DATABASE with FOR ATTACH_REBUILD_LOG. SQL Server then builds a new, empty log file. The method works only when the database was shut down cleanly. This tutorial shows the steps, the Management Studio route and the checks to run afterward.

When You Need This

A data file (.mdf) is the database. The log file (.ldf) holds the record of changes. People end up with only the data file for three reasons. They copied the .mdf and forgot the log. They deleted a huge log to free disk space. Or the disk that held the log failed.

That decides when you can attach a database without log file. Be clear about what you lose. A rebuilt log is new and empty. If the database was shut down cleanly, no data is lost. A clean shutdown writes all changes to the data file first. If it wasn’t, SQL Server refuses to rebuild the log. Then the answer is a backup, not a trick.

Create a Demo Database and Detach It

The demo database has one small table. Detaching removes the database from the server and releases both files, so you can copy or change them. The script writes the file paths first, so you can find them.

IF DB_ID(N'AttachNoLogDemo') IS NULL CREATE DATABASE AttachNoLogDemo;
GO
USE AttachNoLogDemo;
GO
DROP TABLE IF EXISTS dbo.Plant;
CREATE TABLE dbo.Plant (PlantID int PRIMARY KEY, PlantName nvarchar(40) NOT NULL);
INSERT dbo.Plant VALUES (1, N'Basil'), (2, N'Mint'), (3, N'Sage');

USE master;
GO
SELECT name, physical_name FROM sys.master_files WHERE database_id = DB_ID(N'AttachNoLogDemo');
GO
EXEC sp_detach_db N'AttachNoLogDemo';

Now simulate the loss. Open the data folder shown in the file paths. Rename AttachNoLogDemo_log.ldf to AttachNoLogDemo_log.old. Leave the .mdf file where it is. The service account of SQL Server needs access to that folder, as it does for any attach.

Attach With FOR ATTACH_REBUILD_LOG

The script below builds the statement from the instance’s default data path, prints it, and runs it. FOR ATTACH_REBUILD_LOG tells SQL Server to create a new log file when it finds none.

DECLARE @path nvarchar(260) = CONVERT(nvarchar(260), SERVERPROPERTY('InstanceDefaultDataPath'));
DECLARE @sql nvarchar(max) = N'CREATE DATABASE AttachNoLogDemo ON (FILENAME = N''' + @path + N'AttachNoLogDemo.mdf'') FOR ATTACH_REBUILD_LOG;';
PRINT @sql;
EXEC (@sql);

SQL Server prints a File activation failure message, because it can’t find the log. It then reports that a new log file was created. Despite the wording, this is the success path. In the test, the new log was about half a megabyte, against 8 MB for the original. It grows as needed. If you skip the rename, the attach runs without messages, because the log file is still there.

A plain FOR ATTACH also works when the database has a single log file and was shut down cleanly. It prints the same two messages. I prefer ATTACH_REBUILD_LOG, because the statement says what you expect. The old procedure sp_attach_single_file_db still runs on SQL Server 2025, but it’s deprecated. Use CREATE DATABASE.

The Same Result in Management Studio

The Attach dialog does the same work. In SSMS 22, right click Databases in Object Explorer and choose Attach. Click Add and pick the .mdf file. The grid in the dialog lists both files. The log file shows Not found in the Message column. Select that row and click Remove, then click OK. SQL Server builds a new log file.

Check the Database Afterward

Three checks follow every attach. First, look at the owner. The login that runs the attach becomes the owner, and that isn’t always the owner you want. If sa is disabled or renamed on your server, use the login that your policy names. Second, read the data. Third, run DBCC CHECKDB, because a rebuilt log gives SQL Server no history to cross-check.

ALTER AUTHORIZATION ON DATABASE::AttachNoLogDemo TO sa;
SELECT d.name, d.state_desc, SUSER_SNAME(d.owner_sid) AS OwnerLogin FROM sys.databases AS d WHERE d.name = N'AttachNoLogDemo';
SELECT PlantID, PlantName FROM AttachNoLogDemo.dbo.Plant ORDER BY PlantID;
DBCC CHECKDB (AttachNoLogDemo) WITH NO_INFOMSGS;

The database is online, owned by sa, and holds all three rows. CHECKDB prints nothing when it finds no errors. Then take a new full backup. The rebuild starts a new log, so older log backups no longer fit the database.

When the Rebuild Doesn’t Work

The rebuild fails when the database wasn’t shut down cleanly. A crash, a copied file while the service ran, or open transactions at that moment all count. The log is what lets SQL Server finish or undo that work, so it can’t invent a new one. Restore from a backup instead.

Some people want to restore a backup that comes without log backups. A full backup is complete by itself. Restore it with RESTORE DATABASE ... WITH RECOVERY. The log backups, the .trn files, only matter when you want to move forward in time past that full backup. A backup also can’t restore only the data files unless file backups were taken.

You could argue that a tool that repairs a damaged database is the better answer. For a database that was not shut down cleanly, repair options exist, and they can lose data. I treat them as the last step, after every backup has been tried.

What to Remember

Attach a database without log file only when the database was shut down cleanly. Use ATTACH_REBUILD_LOG or the Remove button in SSMS. Then fix the owner, read the data, run CHECKDB and take a full backup.

When you finish, run the cleanup script. It drops the demo database and deletes both of its files. Delete AttachNoLogDemo_log.old by hand, because the script never saw that file.

USE master;
GO
IF DB_ID(N'AttachNoLogDemo') IS NOT NULL
BEGIN
    ALTER DATABASE AttachNoLogDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE AttachNoLogDemo;
END;

A missing log file is not a lost database, it is a clean shutdown that SQL Server can rebuild around.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.