How to Attach MDF Data File Without LDF Log File – Interview Question of the Week #078

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.

How to Attach MDF Data File Without LDF Log File - Interview Question of the Week #078 DatabaseWithoutLogFile0

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.

Solarwinds

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.

How to Attach MDF Data File Without LDF Log File - Interview Question of the Week #078 DatabaseWithoutLogFile1

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.

How to Attach MDF Data File Without LDF Log File - Interview Question of the Week #078 DatabaseWithoutLogFile2

How to Attach MDF Data File Without LDF Log File - Interview Question of the Week #078 DatabaseWithoutLogFile3

How to Attach MDF Data File Without LDF Log File - Interview Question of the Week #078 DatabaseWithoutLogFile4

How to Attach MDF Data File Without LDF Log File - Interview Question of the Week #078 DatabaseWithoutLogFile5

How to Attach MDF Data File Without LDF Log File - Interview Question of the Week #078 DatabaseWithoutLogFile6

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)

Solarwinds
, , , ,
Previous Post
Moving TempDB to New Drive – Interview Question of the Week #077
Next Post
How to Send Execution Plan in Email? – Interview Question of the Week #079

Related Posts

10 Comments. Leave new

  • Thanks Pinal. Even though I was aware of the SSMS methods, I was unaware of the T-SQL method.

    Reply
  • 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

    Reply
  • You can always click the “Script” button in SSMS to see the T-SQL that’s going to be executed

    Reply
  • 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.

    Reply
  • You are the best, Thank you

    Reply
  • 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…

    Reply
  • abhishek thakur
    August 21, 2018 11:37 am

    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.

    Reply
  • 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.

    Reply

Leave a Reply

Menu