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.

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)