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

Issue description: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:MSSQLTestDB_1.mdf',
SIZE = 128MB ,
MAXSIZE = UNLIMITED,
FILEGROWTH = 64MB)
LOG ON
( NAME = N'TestDB_log_file1',
FILENAME = N'E:MSSQLTestDB_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.

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:MSSQLTestDBCopy.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:MSSQLTestDBCopy.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 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:MSSQLTestDB_Repair_1.mdf',           SIZE = 8MB ,            MAXSIZE = UNLIMITED,            FILEGROWTH = 64MB)    LOG ON   ( NAME = N'TestDB_Repair_log_file1',              FILENAME = N'E:MSSQLTestDB_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:MSSQLTestDBCopy.mdf')  ALTER DATABASE [TestDB_Repair] MODIFY FILE(NAME='TestDB_Repair_log_file1', FILENAME= 'E:MSSQLTestDBCopy.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.

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.

Comments

• Anonymous
  April 24, 2015
  Didn't work for me.  I got all the way to the DBCC command and it failed.  I tried messing around with it and starting over, but no luck.

• Anonymous
  April 27, 2015
  Hey Rob, What  have you tried? If you have corrupt mdf file then you can recover your database without any data loss. I had used Stellar Phoenix SQL database tool previously and It did the best job. link: www.stellarinfo.com/sql-recovery.htm