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.

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 (http://blog.sqlauthority.com)

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