You can attach a database with no log file in SQL Server using a single stored procedure call. When the MDF file exists but the LDF is missing, SQL Server can recreate the log file automatically, getting your database back online without needing a backup restore.

Why Would You Need to Attach a Database Without a Log File?

This situation comes up more often than you'd expect in real-world environments. The most common scenarios include:

  • A log file was accidentally deleted or became corrupted
  • A database was moved between servers and only the MDF was copied across
  • Storage failure destroyed the LDF but left the MDF intact
  • A developer handed over a database file without the accompanying log
  • Disk space issues caused the log file to be removed as a quick fix (not recommended, but it happens)

In each of these cases, you have a data file but no transaction log. SQL Server won't attach a database normally if the log file is missing, so you need a different approach.

What Is sp_attach_single_file_db?

sp_attach_single_file_db is a system stored procedure that attaches a database using only the primary data file (MDF). When you run it, SQL Server automatically creates a new transaction log file in the same directory as the MDF. You don't need to specify a log file path or pre-create anything manually.

It's worth noting that Microsoft has marked this procedure as deprecated since SQL Server 2005. It still works in current versions, but the recommended modern approach uses CREATE DATABASE with FOR ATTACH_REBUILD_LOG. Both methods are covered below.

How to Attach a Database with No Log File

Method 1: Using sp_attach_single_file_db (Simple, Deprecated)

This is the quickest option and still functional in SQL Server 2019 and 2022, though Microsoft may remove it in a future release.

EXEC sp_attach_single_file_db 
    @dbname = 'YourDatabaseName',
    @physname = 'C:\SQLData\YourDatabase.mdf';

Replace YourDatabaseName with the name you want the attached database to have, and update the file path to match the actual location of your MDF file on the server.

SQL Server will create a new LDF file in the same folder as the MDF, typically named YourDatabaseName_log.ldf.

This is the current Microsoft-recommended approach for attaching a database with no log file. It gives you more control and doesn't rely on a deprecated procedure.

CREATE DATABASE YourDatabaseName
ON (FILENAME = 'C:\SQLData\YourDatabase.mdf')
FOR ATTACH_REBUILD_LOG;

Again, update the database name and file path to match your environment. SQL Server will rebuild the transaction log in the same directory as the MDF.

If you have multiple data files (MDF plus one or more NDF files), list all of them in the ON clause:

CREATE DATABASE YourDatabaseName
ON 
    (FILENAME = 'C:\SQLData\YourDatabase.mdf'),
    (FILENAME = 'D:\SQLData\YourDatabase_2.ndf')
FOR ATTACH_REBUILD_LOG;

What Happens When SQL Server Rebuilds the Log?

When you use either method, SQL Server creates a brand new, empty transaction log file. This is important to understand. The new log has no history. Any transactions that were in-flight or uncommitted at the time the original log was lost are gone permanently.

SQL Server will attempt to bring the database online, but it may mark it as suspect if it detects the database wasn't shut down cleanly before the log was lost. In that case, you may need to set the database into emergency mode and run DBCC CHECKDB to assess and repair any consistency issues.

This is not a zero-risk operation. If the database was active when the log file disappeared, you could have data integrity problems that won't be immediately obvious.

Before You Attach: Check These Things First

Don't just run the script and hope for the best. Run through this checklist first:

  1. Confirm the MDF is not corrupted. Check the file size looks reasonable and the file isn't showing as 0 bytes.
  2. Check for dirty shutdown. If the database wasn't shut down cleanly, the attach may succeed but leave you with an inconsistent database.
  3. Verify you have a recent backup. If you do, restoring from backup is always the safer option compared to rebuilding the log.
  4. Check SQL Server error logs after attaching. Look for any warnings about database consistency or recovery issues.
  5. Run DBCC CHECKDB after attaching. This should be non-negotiable. Run it before allowing any application to connect.
DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS;

If DBCC CHECKDB comes back clean, you can be reasonably confident the data is intact. If it reports errors, you need to decide whether to repair or restore from backup.

What About the New Log File Size?

SQL Server creates a minimal log file when rebuilding, typically starting at around 512 KB to 1 MB depending on the version. This is far smaller than most production log files should be. Once the database is online, you should review your log file configuration and set it to an appropriate initial size with sensible autogrowth settings.

Leaving a tiny log file in place can cause excessive autogrowth events, which fragment the log and hurt performance. Set it properly from the start.

-- Check current log file size and settings
SELECT 
    name,
    size * 8 / 1024 AS size_mb,
    growth,
    is_percent_growth
FROM sys.database_files
WHERE type_desc = 'LOG';

When Should You Use This Approach vs. Restoring from Backup?

If you have a current backup, restore from it. Full stop. The attach-with-rebuild approach is for situations where a backup either doesn't exist or is significantly out of date and you need to recover whatever data you can from the MDF.

Use FOR ATTACH_REBUILD_LOG or sp_attach_single_file_db when:

  • No backup exists and the MDF is all you have
  • The backup is so old that recovering data from the MDF is worth the risk
  • You're working in a non-production environment and data loss is acceptable
  • A developer or vendor has provided an MDF without a log file

Don't use it as a shortcut in production environments where a clean backup is available. The risk of silent data corruption isn't worth it.

Key Takeaways

  • You can attach a database with no log file using sp_attach_single_file_db or CREATE DATABASE ... FOR ATTACH_REBUILD_LOG. The second method is the current Microsoft recommendation.
  • SQL Server automatically creates a new, empty transaction log file during the attach process. No prior log history is preserved.
  • sp_attach_single_file_db is deprecated and may be removed in a future SQL Server release. Start using FOR ATTACH_REBUILD_LOG in your scripts now.
  • Always run DBCC CHECKDB after attaching a database with a rebuilt log. Data consistency cannot be assumed, especially if the database wasn't shut down cleanly.
  • If a recent backup exists, restore from it instead. Rebuilding the log is a recovery option of last resort, not standard practice.

If you're dealing with missing log files, suspect databases, or recovery scenarios in your environment, these are exactly the situations where having an experienced DBA on call makes a real difference. DBA Services provides SQL Server health checks and managed support for Australian organisations that need reliable, expert-level database administration without the overhead of a full-time hire. Get in touch to find out how we can help.