A developer approached our team who wanted to restore the SQL database. He had only an MDF file present in the system. We suggested him to restore his database from the updated backup copy but, he didn’t have the backup.
This blog demonstrates the effective solution to attach Microsoft SQL database without log files when SQL database gets corrupted. Just follow these steps here below that include different methods by using SQL Server Management Studio, T-SQL, and SQL Database Repair software to attach MDF without LDF.
MDF and LDF Files
Master database file (MDF) and Log transaction files (LDF) are two main files used by SQL Server for saving the database contents. If a database appears abnormal or turns corrupt, you can check transaction logs to determine the cause of the error.
Due to corruption in SQL database, you will find the transaction log file to appear missing, and you will be left with the master database file only.
Note: As per Microsoft guidelines, you cannot restore the database backup from the higher release of the SQL Server to its lower version as by using the Database Backup and Restore strategy, the downgrade is not supported.
It is important to know that a database downgrade from a higher version of the SQL Server to a lower version cannot be accomplished using either the ‘Database Backup & Restore’ technique or by following the ‘Database Detach and Attach’ method. Downgrading a Database to its lower version can only be done by using SQL Server Integration Services.
Ways to Attach Microsoft SQL Server Database Without Log File
There are several ways to attach Microsoft SQL database without using the transaction log:
Permissions Required: To attach the database in the SQL Server, you need to possess membership in db_owner.
Let’s look at these methods:
Method #1: Using SQL Server Management Studio (SSMS)
Here, it is important to mention that the given steps will work only if the database has been clean shutdown and MDF file is available.
Right click on the Database node in the Object Explorer window and then click on Attach.
In the Attach Databases, dialog box that appears click on the Add.
Locate Database Files dialog box will appear, browse through the path where MDF database file is located, click on file to select and then click on OK to exit. A new LDF file will be created by SQL Server while attaching the SQL database. The database will now appear in Databases
Now, return to the Attach Database dialog box. Under database details, you will notice that the SQL Server is unable to find the LDF file.
To attach MDF without LDF file, select the transaction log file and then click on the Remove
Method #2: Using Transact-SQL
You can attach the database to SQL Server using T-SQL when the transaction log is missing. This procedure can be accomplished by using a defined script where you need to mention the name of the database. The T-SQL execution process guarantees a successful attach instance in SQL Server database. The process is given below:
CREATE DATABASE testdb ON
(FILENAME = ‘C:\Program Files\Microsoft SQL Server\..\MSSQL\DATA\testdb.mdf’)
Note: testdb.mdf is the name of my database file. You need to select your file.
Execute DBCC CHECKDB
When MDF file is successfully attached to the SQL Server with missing transaction log file (LDF), you need to execute DBCC CHECKDB for a consistency check, i.e., for checking logical and physical integrity or errors in all the database components.
Likewise, the process to attach the database without transaction log using SQL Server Management Studio works only if the database is cleanly shutdown and healthy MDF exists.
Method #3: Using Stellar Repair for MS SQL
Since both the above-mentioned procedures work only if the master database file is in a healthy state, it is vital to Repair MDF file if an error occurs. Although, a missing log cannot affect the database to a large extent, yet if MDF becomes corrupt, then you will not be able to attach the database.
Stellar SQL Recovery Software processes the master database file and grants access to incorporated data. All the components of the SQL database can be repaired including tables, keys, rules, indexes, defaults, stored procedures, etc. The extensive recovery features include advanced algorithms that allow recovering deleted records from the uploaded file.
Apart from this, the SQL database repair tool allows saving the repaired database components in multiple file formats including XLS, HTML CSV as well as New Database or Live Database. The software is tested and recommended by Microsoft MVPs across the globe. The independent file formats allow accessing database contents without connecting to the Server environment. With the help of the tool, you can also gain access to database contents saved in the secondary database files.
Software testimonial by Daniel Calbimonte(SQL MVP)
The corrupt MDF can be repaired using the application by following three simple steps: Upload, Repair, and Save. Stellar Repair for MS SQL application supports all versions of the application, i.e., SQL Server 2019, 2017, 2016, 2014, 2012, 2008, 2008 (R2), etc. Once the master database file is repaired, you can use it to attach SQL database without transaction log-file using either SQL Server Management Studio or by executing a query in Transact-SQL.