Question: Can you attach an MDF data file when the LDF log file is missing?
Answer: Sometimes. SQL Server can rebuild a missing log for a read/write database that shut down cleanly and has all its data files available. An MDF alone is not a guarantee that attachment or recovery will succeed.
During a performance-tuning exercise, I was explaining why the log file matters. One DBA asked what happens if the LDF is lost or corrupted and they no longer have a useful log file. It is a good interview question because the short answer can easily become dangerous advice.
Keep the original files intact and work on copies. If there is a sound backup, restore from it. Do not delete a present log in the hope that SQL Server will manufacture a better one. An unclean shutdown, missing secondary data file, read-only database or inaccessible file can prevent the rebuild. Rebuilding the log also breaks the existing log-backup chain, so take a new full backup afterward.
The T-SQL method
For a suitable cleanly shut-down database, this template makes the rebuild intent explicit. Replace the database name and file paths:
-- Recovery/attach template only, using copies of cleanly shut-down files.
USE master;
CREATE DATABASE [DatabaseWithoutLogFile]
ON (FILENAME = N'D:\DATA\DatabaseWithoutLogFile.mdf')
FOR ATTACH_REBUILD_LOG;
-- Include every required NDF data file when its path differs.
-- Take a new full database backup after a successful log rebuild.The original example used FOR ATTACH. That can also rebuild a single missing log when its documented conditions are met. Its original screenshot is worth keeping because the “File activation failure” line is followed by confirmation that SQL Server created a new log. Read the entire message, not just its first line:
The SSMS method
In current SSMS, the Attach Databases dialog has an Add control for selecting the MDF and an Associated Files list for checking the required files. The image below shows the empty dialog before any file is selected; it does not show a recovery being performed.
- In Object Explorer, right-click Databases and choose Attach.
- Click Add and select the MDF file. Ensure that the complete required data-file set is available.
- In Database details, select the unavailable log entry and remove that entry from the dialog. This does not mean deleting a physical log file.
- Click OK only after checking the files and recovery conditions. Review all messages and verify the attached database.
If attachment fails, preserve the messages and files. A failure may mean the database needs recovery that a fresh empty log cannot provide. The transaction log records work required for consistency; it is not a disposable companion to the MDF.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
10 Comments. Leave new
Thanks Pinal. Even though I was aware of the SSMS methods, I was unaware of the T-SQL method.
First time commenter, long time fan.
It’s important to note that this only work if the database was properly set offline before detaching, so it will not work if, say, you lost the ldf by removing a drive unexpectedly.
Miguel
You can always click the “Script” button in SSMS to see the T-SQL that’s going to be executed
What additional steps are needed to have the new LDF created n the server non default location for log files? I mean if you want to spcify a different path for the new LDF that will be created. Thanks.
You are the best, Thank you
sir i am trying to attached a database but error is popping up like
database “xyz” cannot be open due to inaccessible or memory space…
but i have lot of space on my drive…
Please suggest some thing to resolved this issue…
You should look at the ERRORLOG to find exact cause. By the way, you have a nice name.
in my Prod scenario database size is 300MB and ldf file size is 100GB.
1) database recovery model is full.
2)we cant take transactional back.
3) is there any way to reclaim for 100GB.
if i use the detached and attached the mdf file with remove ldf file option.is there any datalosse.
kindly suggest.
Rather than rebuilding log file, you need to find the cause of huge LDF and fix that.
Thank you for this. What can you do when the database was not shut down properly and there is not a recent backup? The MDF was moved to new location and attached for ATTACH_FORCE_REBUILD_LOG. But when the command is executed, it is still wanting the LDF from the original path. Its not rebuilding a new one.
Msg 5120, Level 16, State 101, Line 8
Unable to open the physical file “F:\path_to_original_log\Original_log.ldf”. Operating system error 5: “5(Access is denied.)”.
File activation failure. The physical file name “F:\path_to_original_log\Original_log.ldf” may be incorrect.
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. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.
Msg 1813, Level 16, State 2, Line 8
Could not open new database ‘newDatabase’. CREATE DATABASE is aborted.