How To Create Database From Mdf File

Attach SQL Server Database Without Log File

By: | Updated: 2019-04-17 | Comments (20) | Related: More > Database Administration

Troubleshooting the most common SQL Server Problems

Problem

A developer approached me with an MDF file of a SQL Server database which they wanted to restore. This person only had the MDF file for the database and no transaction log file, also they didn't have a backup of the database. In this tip, we will cover how to attach a database that does not have its transaction log files.

Solution

In this tip, we will go through the steps which need to be performed to recover a database when you only have the MDF file of a SQL Server database. The below steps work in for SQL Server 2005 and later.

Using SQL Server Management Studio - Attach a SQL Server Database without a Transactional Log File

1. Connect to SQL Server instance using SQL Server Management Studio

2. In Object Explorer, right click Databases node and select "Attach..." option from the drop down list as shown in the snippet below.

ssms attach database

3. This will open up an Attach Databases window as shown in the below snippet.

ssms attach database settings

4. In the Attach Databases window click on "Add..." button as highlighted in the above snippet to open the Locate Databases Files window as shown below.

5. In Locate Databases Files window you need to browse and locate the MDF file which is relevant to the database you want to attach and then click OK. In our example I am using the "ProductsDB.mdf" file.

database list

6. In the Attach Databases window; you will see that SQL Server informs us that the log file is not found, in our case the missing file is "ProductsDB_Log.LDF". In order to attach the database without the ProductsDB_Log.LDF transaction log file you need to select the file as highlighted in the below snippet and click the "Remove" button. It is possible that the log file will not be listed with the "Not Found" message, so the remove step can be ignored. Finally click OK to attach the ProductsDB database without the transaction log file.

attach database

7. That's it you have successfully attached a database without using the transaction log file. SQL Server will create a new transaction log file for you and will place the file in the same folder as the data file.

Using T-SQL - Attach a SQL Server Database without a Transaction Log File

DBAs can also attach a SQL Server database without a transaction log file using the below T-SQL code.

In this first script we are using the CREATE DATABASE with ATTACH option and specifying the name and location of the mdf file that we have for this database.

USE [master] GO CREATE DATABASE [ProductsDB] ON  ( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\ProductsDB.mdf' ) FOR ATTACH GO

Once the above T-SQL code has executed successfully you will get the below message which informs you that SQL Server has created a new transaction log file for the database.

File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\ProductsDB_log.ldf" may be incorrect.

New log file 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\ProductsDB_log.LDF' was created.

Verify Logical and Physical Integrity of Database

DBAs can check the logical and physical integrity of all the objects within the database by executing a DBCC CHECKDB. In our case we are using the "ProductsDB" database.

DBCC CHECKDB ('ProductsDB') GO

Next Steps

If your database has multiple data files you can use this approach as well to create a new transaction log file.
If your database had multiple transaction log files look at using the ATTACH_REBUILD_LOG option with the CREATE DATABASE command
If you loose one of your data files for a database you can not use this approach. This will only rebuild a new transaction log file for you it does not recreate data files.
Refer to these other Database Administrator tips