Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
Untangle TempDB Performance with SQL Diagnostic Manager - Free Webinar
 

How to Attach a SQL Server Database without a Transaction Log and with Open Transactions


By:   |   Last Updated: 2015-04-22   |   Comments (30)   |   Related Tips: More > Database Administration

Problem

You want to attach a SQL Server database that does not have the transaction log file and you get the following error when you try to attach the data file:

"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."

In this tip I will show how you can successfully attach a database when you get this error.

Solution

Here I will cover the not so uncommon scenario where someone gives you a SQL Server database to attach to your instance, but only gives you the *.mdf file. Unfortunately, when you try to attach the database the SQL Server engine complains about the missing transaction log and aborts the attachment process.

Test Environment Setup

First we will create our sample database and set the recovery model to Full by running the scripts below in SQL Server Management Studio.

USE [master]
GO

CREATE DATABASE [TestDB]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'TestDB_file1', 
			FILENAME = N'E:\MSSQL\TestDB_1.mdf',
			SIZE = 128MB , 
			MAXSIZE = UNLIMITED, 
			FILEGROWTH = 64MB) 
 LOG ON 
( NAME = N'TestDB_log_file1',
			 FILENAME = N'E:\MSSQL\TestDB_1.ldf',
			 SIZE = 8MB,
			 MAXSIZE = 2048GB,
			 FILEGROWTH = 8MB)
GO

ALTER DATABASE TestDB SET RECOVERY FULL
GO

The next script will create our sample table.

USE TestDB
GO

SELECT  *
INTO    TestTable
FROM    sys.objects	   

Now we are going to add some sample data. We want the insert statement to take enough time to let us force the shutdown of the test instance while it is still running. This will let the database be in an inconsistent state needing to perform recovery at the next database startup.

USE TestDB;
GO

INSERT INTO dbo.TestTable
SELECT  a.*
FROM    TestTable a
        CROSS JOIN sys.objects b
        CROSS JOIN sys.objects c
        CROSS JOIN sys.objects d

In another window in SQL Server Management Studio execute the following statement to force the instance shutdown.

SHUTDOWN WITH NOWAIT

After stopping the instance, delete the log file then start up the SQL Server service. If you refresh the Databases view in SQL Server Management Studio you will see that our test database is inaccessible because it is marked as Recovery Pending, as shown on the next image.

Recovery Pending Database.

At this point we have an orphaned and inconsistent database file.

First, letís clean the system catalog by dropping the database. We must set the database offline to copy or rename the data file that will be the subject for our tests.

USE master
GO

ALTER DATABASE TestDB SET OFFLINE 
GO

Then we must clean the system catalog metadata by dropping the database.

USE master
GO

DROP DATABASE TestDB
GO

Trying to Attach the Damaged SQL Server Database

When you are asked to attach a database with one data file and no log, the first thing that may come to mind is the old and deprecated sp_attach_single_file_db.

USE master 
GO 

EXEC sys.sp_attach_single_file_db @dbname = 'TestDB', 
    @physname = N'E:\MSSQL\TestDBCopy.mdf' 
GO

But after you execute the previous script you will see that it fails with this error message:

"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."

See the image below as a point of reference.

Failed Attempt To Attach the Database.

Since sp_attach_single_file_db is deprecated and has been replaced with CREATE DATABASE..FOR ATTACH, letís try this to see if we have more luck.

USE [master]
GO
CREATE DATABASE [TestDB] ON 
( FILENAME = N'E:\MSSQL\TestDBCopy.mdf' )
 FOR ATTACH_REBUILD_LOG 
GO

We face the same error message telling us that the log of the database cannot be rebuilt.

Another Failed Attempt To Attach the Database.

At this point we tried everything, but there is another way, make the engine believe that the database is already attached.

Attaching the Damaged SQL Server Database

The first step is to create a new database.

USE [master]
GO

CREATE DATABASE [TestDB_Repair]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'TestDB_Repair_file1', 
			FILENAME = N'E:\MSSQL\TestDB_Repair_1.mdf',
			SIZE = 8MB , 
			MAXSIZE = UNLIMITED, 
			FILEGROWTH = 64MB) 
 LOG ON 
( NAME = N'TestDB_Repair_log_file1',
			 FILENAME = N'E:\MSSQL\TestDB_Repair_1.ldf',
			 SIZE = 8MB,
			 MAXSIZE = 2048GB,
			 FILEGROWTH = 32MB)
GO

Now we set the database offline.

USE master
GO

ALTER DATABASE [TestDB_Repair] SET OFFLINE WITH ROLLBACK IMMEDIATE
GO

At this point we can change the file location of our new database to point to our orphaned mdf file and set the location of the log file to a non-existent file.

USE master
GO

ALTER DATABASE [TestDB_Repair] MODIFY FILE(NAME='TestDB_Repair_file1', FILENAME= 'E:\MSSQL\TestDBCopy.mdf')
ALTER DATABASE [TestDB_Repair] MODIFY FILE(NAME='TestDB_Repair_log_file1', FILENAME= 'E:\MSSQL\TestDBCopy.ldf')
GO

Letís bring the database back online.

USE master
GO

ALTER DATABASE [TestDB_Repair] SET ONLINE
GO

We donít have to be SQL Server gurus to know that the previous script will fail. But if you take a look at the error message of the next screen capture you will see that when SQL Server didnít find the transaction log file (remember that we changed the system catalog to point to a file that doesnít exist) it tries to rebuild it. And of course its attempt to rebuild the log fails with the same error message we had while trying to attach our orphaned *.mdf file, only in this case the *.mdf was successfully attached leaving us one step closer to our objective.

Bringing the Database Online.

Rebuilding the SQL Server Transaction Log

Now you will see that it isnít very complicated to rebuild the SQL Server transaction log, but you must accept the fact that you will lose data. In fact, you should use this method to recover a damaged database if restoring the database from a backup is not possible. The reason behind this is that you can lose data other than the last user activity. For example, if a transaction was updating an index and the update operation performed a page split, you may lose previously committed transactions that were no longer in the transaction log because page splits are a logged operation.

The next script includes several commands that I put together to bring our sample database back online. Further on I will explain the commands and why I decided to execute all of them in a single script, but first let's take a look.

USE master
GO

DBCC TRACEON(3604)
GO

ALTER DATABASE TestDB_Repair SET EMERGENCY
GO

ALTER DATABASE TestDB_Repair SET SINGLE_USER
GO

DBCC CHECKDB('TestDB_Repair', REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS
GO

ALTER DATABASE TestDB_Repair SET MULTI_USER
GO

The first step in the previous script is to send all output from the DBCC commands to the query results instead of to the error log. The next two steps set the database to emergency mode and single user mode respectively, so we can execute DBCC CHECKDB with the REPAIR_ALLOW_DATA_LOSS option. Finally the last command is to bring the database back to multi user mode.

On the next image you can see a screen capture of the execution of the previous script. I marked in red one of the output messages which states that the error log has been rebuilt.

CheckDB Execution.
Next Steps


Last Updated: 2015-04-22


next webcast button


next tip button



About the author
MSSQLTips author Daniel Farina Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Tuesday, October 16, 2018 - 9:58:03 PM - Darren Back To Top

†Wow dude! This seriously saved me!! Thank you so much!!


Thursday, October 04, 2018 - 1:38:18 PM - Allen Back To Top

†Thanks.† DPM restored the .mdf and .ldf with transactions in flight.†
Also - had to change security for the files so MSSQLSERVER could use the files.†


Monday, August 20, 2018 - 11:09:12 AM - John Smith Back To Top

Excellent guide, got us out of a really sticky situation where the log file in a backup was corrupted/too large to restore.

Thanks!


Thursday, July 12, 2018 - 6:19:33 AM - Tajudeen Back To Top

Hi,

I tried the steps that you have mentioned.†

I am receiving the below error after executing the command

"

† DBCC CHECKDB('TestDB_Repair', REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS

† GO

"

error msg:

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.

No luck for me.†It will be very helpful if you provide any lead on this issue.

Thanks in advance.


Wednesday, March 14, 2018 - 2:08:53 PM - Divya Back To Top

 Thank you so much this post saved my life!!!

 


Thursday, January 25, 2018 - 6:20:22 PM - Ryan Back To Top

Wow, this worked for me.  I really appreciate it.

Not sure if it should have mattered, but for attaching a MDF with missing LDF.  I had to put the file at the same local path as the original computer initially.  Such as "Program Files" vs "Program Files(x86)".  Both were 32-bit SQL 2012.

Thank you!

-Ryan 


Sunday, December 17, 2017 - 4:32:31 AM - Oleg Kovalev Back To Top

Thank you !


Wednesday, November 15, 2017 - 2:43:17 PM - Gabriel Uribe Back To Top

Thank you, great tip


Friday, November 03, 2017 - 6:34:03 PM - Jo„o Paulo Back To Top

Hello Daniel, thank you a lot, you saved my life =)


Tuesday, September 26, 2017 - 2:57:05 PM - Sunil Choudhary Back To Top

 

 Thanks allot you save my life.. :)


Wednesday, September 20, 2017 - 6:59:54 AM - Shivanand Koli Back To Top

Thnx a lot  

 


Wednesday, May 24, 2017 - 7:55:27 AM - Ragav Back To Top

Or we can simply force to create a ldf by using 'ATTACH_FORCE_REBUILD_LOG' as below

 

USE [master]
GO
CREATE DATABASE [TestDB] ON 
( FILENAME = N'E:\MSSQL\TestDBCopy.mdf' )
 FOR ATTACH_FORCE_REBUILD_LOG  

GO 

 

 


Sunday, April 30, 2017 - 11:21:59 AM - Dennis Back To Top

These detailed steps helped me when I lost any hope!

 Thank you very much, Daniel!!

 


Thursday, January 05, 2017 - 9:38:29 AM - YMO Back To Top

 This is awesome, thank you!!! It help me attached the databases I thought it's not possible to attchach again. 

 


Sunday, October 23, 2016 - 12:52:51 PM - MT Back To Top

This tip has helped me restore our client database. Thank you so much for publishing such a wonderful step-by-step approach!


Friday, September 16, 2016 - 2:02:59 AM - Jyoti Varma Back To Top

Thanks Daniel, your detailed steps made me recover my corrupted database which was down since 3 weeks. Your instructions and errors described in each steps helped me understand each step.

 


Friday, July 29, 2016 - 2:39:38 PM - TF Back To Top

 Thank you very much for putting this together.  Worked perfectly!

 


Wednesday, April 20, 2016 - 6:44:13 AM - Dar Back To Top

Brilliant


Friday, March 25, 2016 - 4:26:45 PM - Ross Presser Back To Top

Note that the path of the original LDF file must exist. For example, if it was D:\SQL\DATA on the original server, you must have a D:\SQL\DATA path on the recovery server. Otherwise the DBCC CHECKDB step will fail.  

 


Wednesday, February 24, 2016 - 3:50:49 AM - sepehr Back To Top

thank you soooo much sir,

in the last step I receive the  error below:

'DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Msg 5180, Level 22, State 1, Line 7

Could not open File Control Block (FCB) for invalid file ID 3 in database 'TestDB_Repair'. Verify the file location. Execute DBCC CHECKDB.'

 

would you mind helping me again please?

thanks in advance


Monday, February 22, 2016 - 7:01:38 AM - Arvind Back To Top

 Thank you very much for this post. really helped save hours of my time. 


Wednesday, February 03, 2016 - 10:09:43 PM - Vivien Back To Top

 This article helped me a lot. Thank you for the time and effort you put into creating this. Very much appreciated. Really great and detailed. Thanks!!!

 


Friday, November 06, 2015 - 12:24:54 PM - Ariel Back To Top

Este genial artículo me acaba de salvar de un buen problema. Muchas gracias.


Wednesday, September 16, 2015 - 8:27:11 AM - Andreas Back To Top

Hi,

thanks for this tip, I think it safed my life. I try 3 days to restore a database. And with your tips, everything works fine.

Thanks a lot

Best regards,

Andreas


Friday, May 01, 2015 - 5:35:54 AM - Stephen Back To Top

Welcome Greg


Wednesday, April 29, 2015 - 6:51:35 AM - Greg Robidoux Back To Top

Stephen, thanks for letting us know the tip was copied onto another site.

-Greg Robidoux


Wednesday, April 29, 2015 - 12:52:59 AM - Stephen Back To Top

This post is also at MSDN blog.

http://blogs.msdn.com/b/sql/archive/2015/04/23/how-to-attach-a-sql-server-database-without-a-transaction-log-and-with-open-transactions.aspx?CommentPosted=true#commentmessage

 


Thursday, April 23, 2015 - 4:00:58 PM - Jan Back To Top

You da man!


Thursday, April 23, 2015 - 9:54:03 AM - Jeff Back To Top
This is a great article and I will keep it as part of my disaster recovery plan and knowledge

Thank you very much

Thursday, April 23, 2015 - 1:03:10 AM - Patrick Flynn Back To Top

This tip is a specific case of a more general technique that was covered in
http://www.sqlskills.com/blogs/paul/disaster-recovery-101-hack-attach-a-damaged-database/

 

This covers the risks involved in this procedure in more detail


Learn more about SQL Server tools