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

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 two methods that 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.

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.

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.

At this point we tried everything, but there are two methods we can use to recover the database, and I will cover both. The first method requires the usage of an undocumented command in the CREATE DATABASE statement. As you may know, the use of undocumented statements is not supported by Microsoft, so if something goes wrong you can’t contact them. The other method is trickier, but does not use undocumented features.

The Undocumented ATTACH_FORCE_REBUILD_LOG Command

By now we know that if we use the CREATE DATABASE..FOR ATTACH statement to recover the database it will fail because we do not have the transaction log file. Also, since there were open transactions in the database, we cannot use the ATTACH_ REBUILD_LOG command. There is an undocumented ATTACH_FORCE_REBUILD_LOG Command that as its name suggests, forces the CREATE DATABASE..FOR ATTACH statement to recover the database even when there were orphaned opened transactions. Basically, it creates a new log file.

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

In the next image we can see the execution of the previous command.

Attaching the Damaged SQL Server Database Without Using Undocumented Features

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 do not 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 does not find the transaction log file (remember that we changed the system catalog to point to a file that does not 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.

Rebuilding the SQL Server Transaction Log

Now you will see that it is not 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 the 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.

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

Next Steps

• Before running an integrity check of your databases I recommend you read this tip: SQL Server Database Integrity Checks Checklist.
• To get a detailed explanation about DBCC CHECKDB take a look at this tip: Minimize performance impact of SQL Server DBCC CHECKDB.
• This tip will explain how Emergency mode works: Using the Emergency State for a Corrupt SQL Server Database.
• If you don’t know the differences between recovery models take a look at this tutorial: SQL Server Recovery Models Tutorial.
• You can learn transaction log basics and more in this tutorial: Introduction to the SQL Server Transaction Log.
• Here you can learn about attaching and detaching databases: Upgrading to SQL Server 2008 Using Detach and Attach Operations.