Attach a Database without a Transaction log file Follow


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.

The 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'TestDBfile1',
FILENAME = N'E:\MSSQL\TestDB
1.mdf',
SIZE = 128MB ,
MAXSIZE = UNLIMITED,
FILEGROWTH = 64MB)
LOG ON
( NAME = N'TestDBlogfile1',
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 Damage 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 spattachsinglefiledb.

USE master
GO

EXEC sys.spattachsinglefiledb @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 spattachsinglefiledb 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 ATTACHREBUILDLOG
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 [TestDBRepair]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'TestDB
Repairfile1',
FILENAME = N'E:\MSSQL\TestDB
Repair1.mdf',
SIZE = 8MB ,
MAXSIZE = UNLIMITED,
FILEGROWTH = 64MB)
LOG ON
( NAME = N'TestDB
Repairlogfile1',
FILENAME = N'E:\MSSQL\TestDBRepair1.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 [TestDBRepair] MODIFY FILE(NAME='TestDBRepairfile1', FILENAME=
'E:\MSSQL\TestDBCopy.mdf')
ALTER DATABASE [TestDB
Repair] MODIFY FILE(NAME='TestDBRepairlog_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.

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 TestDBRepair SET EMERGENCY
GO

ALTER DATABASE TestDB
Repair SET SINGLEUSER
GO

DBCC CHECKDB('TestDB
Repair', REPAIRALLOWDATALOSS) WITH ALLERRORMSGS
GO

ALTER DATABASE TestDBRepair SET MULTIUSER
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 REPAIRALLOWDATA_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.

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




Have more questions? Submit a request

Comments

Powered by Zendesk