Attaching a SQL Server Database Using Only the MDF File

You can attach a SQL Server database using only the MDF file, without a matching LDF log file. SQL Server provides two methods to do this: the legacy sp_attach_single_file_db stored procedure, and the more flexible CREATE DATABASE ... FOR ATTACH_REBUILD_LOG syntax. The right method depends on how many log files the original database had.

This situation comes up more often than you'd expect. A log file gets corrupted. A drive fails and takes the LDF with it. Someone hands you a backup of just the MDF from an old server that's already been decommissioned. Whatever the cause, you need to get the database online without the original transaction log, and you need to do it cleanly.

Here's how to do it correctly, and what to watch out for along the way.

When Would You Need to Attach Only an MDF File?

The most common scenarios are:

  • Log file corruption or deletion - The LDF was lost due to disk failure, accidental deletion, or storage issues
  • Legacy database migrations - Someone archived only the MDF from an old system, not realising the LDF was also needed
  • Development and testing - A developer needs to spin up a copy of a production database and only has the data file
  • Disaster recovery situations - Partial backups or incomplete file transfers left you with an MDF but no matching LDF

In all of these cases, SQL Server needs to rebuild the transaction log from scratch. This is a controlled process, but it does come with some important caveats covered below.

Method 1: Using sp_attach_single_file_db

The sp_attach_single_file_db stored procedure is the traditional approach for attaching a database from a single MDF file. It instructs SQL Server to attach the data file and automatically create a new transaction log file.

The syntax is straightforward:

EXEC sp_attach_single_file_db 
    @dbname = 'YourDatabaseName', 
    @physname = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Data\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 disk.

Important limitation: This method only works when the original database had a single log file. If the database previously had multiple LDF files, sp_attach_single_file_db will fail. In that case, use Method 2.

It's also worth noting that Microsoft has marked sp_attach_single_file_db as deprecated. It still works in current versions of SQL Server, but Microsoft recommends using CREATE DATABASE ... FOR ATTACH_REBUILD_LOG going forward. Don't build new processes around the deprecated procedure.

Method 2: Using CREATE DATABASE FOR ATTACH_REBUILD_LOG

This is the preferred method and works regardless of how many log files the original database had. The FOR ATTACH_REBUILD_LOG clause tells SQL Server to attach the data file and rebuild the transaction log automatically.

USE [master]
GO

CREATE DATABASE [YourDatabaseName] ON
(
    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Data\YourDatabase.mdf'
)
FOR ATTACH_REBUILD_LOG
GO

Update the FILENAME path to match the actual location of your MDF file. The database name in the CREATE DATABASE statement can be anything you like, it doesn't need to match the original database name.

If the original database had multiple data files (NDF files as well as the MDF), you'll need to include all of them in the ON clause. For example:

USE [master]
GO

CREATE DATABASE [YourDatabaseName] ON
(
    FILENAME = N'D:\Data\YourDatabase.mdf'
),
(
    FILENAME = N'D:\Data\YourDatabase_2.ndf'
)
FOR ATTACH_REBUILD_LOG
GO

SQL Server will create a new LDF file in the same directory as the MDF by default. If you want the log file in a different location, you'll need to move it after the attach completes, or use the full CREATE DATABASE ... FOR ATTACH syntax with an explicit log file path instead.

What Happens When SQL Server Rebuilds the Log?

Understanding what actually happens during log rebuild helps you assess the risk.

When SQL Server attaches a database using FOR ATTACH_REBUILD_LOG, it creates a brand new, empty transaction log. The database comes online in a clean state, but any transactions that were in-flight at the time the original database was last shut down may not be fully committed. SQL Server will mark the database as having been through an unclean shutdown and run recovery automatically.

In most cases this works fine, particularly if the original database was shut down cleanly. If the database was shut down mid-transaction or the MDF came from a crashed instance, you may see errors during recovery. In those situations, the database might come online but flag certain pages as suspect, or recovery might fail entirely.

This is why attaching only an MDF file is generally acceptable for development, testing, and migration scenarios, but shouldn't be your primary recovery strategy for production databases. If you're dealing with a genuine production outage, a proper restore from a verified backup is always the better path.

Step-by-Step Process for Attaching an MDF Without a Log File

  1. Confirm you have the MDF file and note the full file path on the SQL Server host
  2. Check for NDF files - if the original database had secondary data files, you'll need those too
  3. Verify file permissions - the SQL Server service account needs read/write access to the MDF file and its directory
  4. Choose your method - use sp_attach_single_file_db for simple single-file databases, or CREATE DATABASE ... FOR ATTACH_REBUILD_LOG for everything else
  5. Run the T-SQL against the master database on the target SQL Server instance
  6. Verify the attach - check the database comes online and is accessible
  7. Check for errors - review the SQL Server error log for any recovery warnings after the attach completes
  8. Back up immediately - take a full backup of the newly attached database before doing anything else with it

Common Errors and How to Fix Them

"Cannot attach the file because it is already in use" - Another SQL Server instance already has this file attached, or a file lock is held by another process. Detach from the other instance first, or check for file locks using Sysinternals Process Explorer.

"Access is denied" - The SQL Server service account doesn't have permissions to the MDF file. Grant the service account read/write access to the file and its parent directory.

"The log cannot be rebuilt because there were open transactions/user connections at the last shutdown" - The database wasn't shut down cleanly. Recovery may still succeed, but check the error log carefully afterwards. In some cases you may need to use emergency mode and DBCC CHECKDB to recover the database.

"File activation failure" - Usually means SQL Server can't find one of the secondary data files (NDF files). Locate the missing NDF files and include them in the CREATE DATABASE statement.

Key Takeaways

  • You can attach a SQL Server database using only the MDF file by using either sp_attach_single_file_db or CREATE DATABASE ... FOR ATTACH_REBUILD_LOG, both of which instruct SQL Server to create a new transaction log automatically
  • FOR ATTACH_REBUILD_LOG is the preferred and more flexible method, working with databases that originally had multiple log files and avoiding the deprecated stored procedure
  • If the original database had secondary data files (NDF files), all data files must be included in the attach command, not just the MDF
  • Log rebuild is safe for development and migration scenarios, but for production recovery, always prefer restoring from a verified backup
  • After attaching, take a full backup immediately and review the SQL Server error log for any recovery warnings

If you're regularly dealing with database recovery situations, missing files, or uncertain backup integrity, it's worth having a proper review of your backup and recovery strategy. DBA Services provides SQL Server health checks and managed support for organisations across Australia, helping you identify gaps before they become outages. Get in touch to find out how we can help.