There can be a situation where you don’t have full database backup and you have corrupted your ldf file due to power shutdown (or some one intentionally deleted the ldf file). And you are left with only the data file (i.e mdf file). You can still attach your mdf file with database and recover it.
Below is an example of how, you can attach a database without ldf file.
Database Name : AdventureWorks
Data File:
C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorks.mdf
Log File: Not Available(.LDF)
Using SSMS : Attaching database without LDF File
- Connect to the SQL server instance using SSMS
- From the Object Explorer, right click the Databases node and a drop-down menu will be appeared.
- Now click on the Attach tab.
- Click on the Add button from appeared dialog box.
- Now a Locate Database File, select it and click ok. Now you will see your mdf file is selected but SQL server is unable to select your transaction log file because transaction log file was deleted.
Now if you click OK, it will fail with the operating system error ( unable to open the physical file)
- To attach .mdf file without transaction log file; select the log file and click on the remove button to remove it.
- Now click on the OK button to attach the database file. SQL server will create a transaction log file for you while attaching the database.
- once you refresh the SSMS you will get the attached database .
hope it will help you out !!!!!