Problem Statement: One day I downloaded adventureworks.mdf file from Microsoft website and tried to attach the mdf file through SQL Server management studio. But I was getting below error message while attaching the mdf file. Hence I thought to share the resolution with my blog readers.
Solution: In this article, I will show how to attach a database file(.mdf) without having a log file(.log). We will click start menu and right click on the SQL server management studio menu and open it using “Run as Administrator” option(As shown below).
Once the management studio is opened, we will right click on “Databases” node and then select the “attach” menu. As soon as we click on “Attach” menu, a new pop up window will appear as shown below.
Now we will click on “Add” button (shown above) that will bring a new pop up window as shown below.
we will locate the .mdf file and click on “OK” button that will bring us back to the earlier screen as shown below.
In the above screenshot, we can see in the bottom grid there are two records shown, one is for .mdf file and the other is for .ldf file. Since we do not have .ldf file, In message column, we are seeing a message “Not Found“. We will select the log file row and delete from the grid by clicking on “Remove” button. Once the log file record is removed from the grid, we will click on the “OK” button that will attach the database.
Attaching data file(.mdf) using T-SQL: Below is the T-SQL script for attaching data file.
CREATE DATABASE MyDb ON (FILENAME = 'E:\Downloads New\mydb.mdf') FOR ATTACH;
Note: Please do not forget to open management studio using “Run As Administrator” option otherwise you will get error message related to permissions.
I hope you will like this article.
794 total views, 2 views today