Attach SQL Server Database Without Log File

By:   |   Comments (19)   |   Related: More > Database Administration


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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Ashish Kumar Mehta Ashish Kumar Mehta has been contributing to the MSSQLTips.com community since 2009 with over 60 tips.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Thursday, March 5, 2020 - 8:41:05 AM - Greg Robidoux Back To Top (84950)

Hi Luis,

did you take a look at this tip to see if this helps: https://www.mssqltips.com/sqlservertip/3579/how-to-attach-a-sql-server-database-without-a-transaction-log-and-with-open-transactions/

-Greg


Wednesday, March 4, 2020 - 11:17:38 PM - LUIS Back To Top (84935)

File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL10_50.COMPAC\MSSQL\DATA\DB_Directory_log.LDF" may be incorrect.

The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.

Msg 1813, Level 16, State 2, Line 4

Could not open new database 'DB_Directory'. CREATE DATABASE is aborted.


Wednesday, August 28, 2019 - 1:55:34 PM - Igor Micev Back To Top (82171)

Hi,

If you have in-memory file groups in your database then you get the following

Error message: Rebuilding the log file is not supported for databases containing memory-optimized tables.

The article doesn't cover that scenario.


Monday, April 22, 2019 - 9:24:57 AM - noone Back To Top (79633)

I realize this is an old post, and perhaps the issue is version realted. But I recently had this same issue and resolved it on SQL 2016 with this.

CREATE DATABASE [dbname] ON
(FILENAME = N'directory\filename.mdf')
FOR ATTACH_REBUILD_LOG

I do not recall where I learned it.


Friday, May 18, 2018 - 9:02:02 AM - Dipali Back To Top (75976)

I HAVE FEW DATABASE FILES (MDF CREATED IN 2000) SO  I INSTALL IN SQL SERVER 2008 R2 ADVANCED SP2, BUT STILL NOT ABLE TO OPEN FILES. GETTING BELOW ERROR...AND ALSO TRIED WITHOUT LDF FILES BUT NOT WORKED..I HAVE BACKUP FILES THAT I HAVE RESTORED BUT I WANT TO WORK ON MDF FILE.

PLEASE HELP ASAP. ITS VERY IMPORTANT.

The log scan number (20845:218:1) passed to log scan in database 'HM' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup. Could not open new database 'HM'. CREATE DATABASE is aborted. (.Net SqlClient Data Provider)..


Sunday, February 21, 2016 - 6:31:58 PM - Ron Back To Top (40739)

 I followed your dirctions and (finally) that worked!!!! Perfectly. THANKS!!!

 


Wednesday, August 19, 2015 - 9:11:15 PM - bala Back To Top (38492)

Excellent i never expect. very very very very usefull thanks a lot


Wednesday, December 10, 2014 - 12:37:51 PM - Shane Back To Top (35558)

Thank you!


Tuesday, June 24, 2014 - 10:45:03 AM - nagesh Back To Top (32373)

Dear Author and all,

While attaching a database which contains 16 mdf and 4ldf files, iam facing the below errors. as per the below error i have tried lot some methods including your article but there is no use. Kindly suggest me to bring back the database. 

 

File activation failure. The physical file name "O:\DBFolder\Testdb_LOG2.ldf" may be incorrect.

The log cannot be rebuilt because the database was not cleanly shut down.

Msg 1813, Level 16, State 2, Line 3

Could not open new database ' Testdb '. CREATE DATABASE is aborted.

Msg 823, Level 24, State 6, Line 3

The operating system returned error 38(Reached the end of the file.) to SQL Server during a read at offset 0000000000000000 in file ' O:\DBFolder\Testdb_LOG2.ldf'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

 

Thanks in advance,

Nagesh

 


Sunday, February 16, 2014 - 3:26:04 AM - Rahul Back To Top (29468)

Set Database to offline state, Dettach the database and then re-attach the database. Check your DATA file...you will find a .LDF file :)


Friday, February 14, 2014 - 4:53:39 AM - SQLSACT Back To Top (29445)

 

This solution works great if the database was shutdown cleanly, if not then the database will have to be hacked into SQL Server

I believe Gail Shaw has an excellent article that highlights this.


Wednesday, October 2, 2013 - 12:13:38 PM - Bill Back To Top (27011)

Thanks for the article; but what about SQL 2000?  I have this exact problem and was helping a buddy migrate his small business.  They have SQL 2000, and deleted transaction log.  I only have the MDF.  What can I do in sql 2000 SP3?


Tuesday, July 16, 2013 - 7:49:33 AM - Vinay Back To Top (25852)

This article resolved my problem. Thanks.


Saturday, November 17, 2012 - 1:28:33 AM - Arup Back To Top (20387)

File activation failure. The physical file name "E:\ASWEBTECH\SaradaModelDB-2011-2012_log.ldf" may be incorrect.

New log file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\SaradaModelDB_log.LDF' was created.

Msg 1813, Level 16, State 2, Line 1

Could not open new database 'SaradaModelDB'. CREATE DATABASE is aborted.

Msg 824, Level 24, State 2, Line 1

SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x66666666; actual: 0x33333333). It occurred during a read of page (1:1202) in database ID 6 at offset 0x00000000964000 in file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\SaradaModelDB.mdf'.  Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

 
 
Hi have this problem. please help.

Saturday, November 3, 2012 - 3:43:39 AM - Robert Hamill Back To Top (20201)

HI

This is the best site for SQL explanations and troubleshooting I have found.

With this article, you have again saved me hours of research and work

Thank you - keep up good work :)

 

Bob Hamill


Friday, July 6, 2012 - 7:38:22 AM - Thinus Back To Top (18369)

Hi this is very nice, I have a question if you only have the Log file is it posible to recover the log information if you don't have the .mdf file.

 

Our It Deleted the .mdf file and I only have the log file it's kind of urgent if their is a way please let me know.

 

Will be appreciated.


Monday, April 2, 2012 - 3:31:04 AM - Krishnaraj Back To Top (16728)

Above solution will work only in case if DB is cleanly shutdowned else DB server will throw error with message "The log cannot be rebuilt because the database was not cleanly shut down". Please let me know if you have any solution for this problem.

Thank you.


Sunday, March 25, 2012 - 4:41:06 PM - shivrudra Back To Top (16604)

hI,

 

IF YOU ENCOUNTER ANY PROBLEM OF ACCESS DENIED WHILE ATTACHING SAME START SMS BY RUN AS ADMINISTRATOR AND TRY AGAIN

 


Thursday, July 28, 2011 - 9:48:47 PM - rodoss Back To Top (14285)

It solved my problem!

Thank you.















get free sql tips
agree to terms