Just the other day I was busy during a performance tuning exercise I explained to the team of DBA that how much log file is critical and important for their performance. Once I was explaining the importance and role of the LDF file in the performance, one of the DBA from the team asked me question about what would they do if they somehow lose the LDF file or just have corrupted log file which they do not care now. Well, I though it was a very important question and I believe it can be a very good interview question as well. Let us learn about how to attach the MDF data file without LDF log file.
Question: How to Attach MDF Data File Without LDF Log File?
Answer: Yes, it is absolutely possible to do that if we have MDF file.
We can do this with T-SQL Script as well as with SQL Server Management Studio. Let us learn both the methods one after another.
Method 1) T-SQL Way
USE [master] GO CREATE DATABASE [DatabaseWithoutLogFile] ON ( FILENAME = N'D:\DATA\DatabaseWithoutLogFile.mdf' ) FOR ATTACH GO
You can run above script and it will attach your database MDF file to server and recreate the new log file.
Method 2) SQL Server Management Studio
Here is the detailed screenshot walk-through for how you attach your database without log file in SQL Server Management Studio.
If you attempt both the methods and if you still have a problem, please leave a comment or reach out to me and I will be happy to help you to fix the problem.
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
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…
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.
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.