SQL SERVER – Transaction Log Backup Failing After Rebuilding Database Log File

Speaking at community events get me really energized. It is wonderful to connect with people who build some of the greatest code ever for their respective organizations. Last weekend, I was at a conference in Delhi and it was an awesome opportunity for me to meet a number of you. I would take a moment to Thank all who took a minute to walk to me and talk to me about your experience. These experiences are what make me who I am.

Coming out of the conference I had this one moment that I think just made me work on something quick and I thought this is worth a share as a blog. This was a corridor conversation during the first day end that this DBA walked up to me and said, if I was up for a challenge as they were facing some problem recently in their deployment. After doing a couple of sessions, I was a bit tired but this challenge got me thinking. I stopped and said, I am game to hear you. I will try to answer and if I can’t, meet me first thing tomorrow morning and we should have something.

He started by saying – “Pinal, our transaction log backups are failing.”

Pinal: That is a start, you need to let me know what the error is.

DBA: Well, it is part of our test box. We have a maintenance plan and it started to fail. So there is no error that I can see yet.

Pinal: Was it working in the past?

DBA: Oh yeah!! It used to work and now the maintenance plans are not working I think.

Pinal: Did you guys do anything different on this box before this error started surfacing?

DBA: Hmmm … Last week we had a bad hard disk corruption. We detached the database and then we replaced the HDD. Then we attached the DB back.

Pinal: Is there anything that you folks did differently while attaching?

DBA: Oh yeah, when the HDD crashed – we lost the Log file and so when we attached we just rebuilt the Log.

Pinal: Well, you have the answer with you. That is the problem. Did you know that?

DBA: Is it? It was configured as Full recovery model. Everything was fine Pinal.

Pinal: You are correct, I want to show you what happened behind the scene. Meet me tomorrow.

That evening I crashed early and got up early to bring a repro to this problem. I love such challenges that make me learn.

So here is the steps to reproduce this situation.

Step 1: Create Database and make it FULL recovery model

This is the simple part.

CREATE DATABASE [RebuildLogDB]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'RebuildLogDB', FILENAME = N'C:\Rebuild\RebuildLogDB.mdf', 
SIZE = 10240KB, FILEGROWTH = 1024KB) 
LOG ON
( NAME = N'RebuildLogDB_log', FILENAME = N'C:\Rebuild\RebuildLogDB_log.ldf', 
SIZE = 5120KB, FILEGROWTH = 1024KB)
GO
ALTER DATABASE [RebuildLogDB] SET RECOVERY FULL
GO

-- Take a FULL backup to make it truly Full Recovery Model
BACKUP DATABASE [RebuildLogDB] TO DISK = N'NUL'
WITH NOFORMAT, NOINIT, NAME = N'RebuildLogDB-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

Step 2: Detach the Database

We will detach the Database and then we will go ahead and delete the Log file manually from the folder.

-- Detach the DB
USE [MASTER]
GO
ALTER DATABASE [RebuildLogDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [MASTER]
GO
EXEC MASTER.dbo.sp_detach_db @dbname = N'RebuildLogDB'
GO

Step 3: Attach the DB without Log file

-- Attach without the Log file.
USE [master]
GO
CREATE DATABASE [RebuildLogDB] ON
( FILENAME = N'C:\Rebuild\RebuildLogDB.mdf') 
FOR ATTACH
GO

On executing the above, we will be given a warning message like below:

File activation failure. The physical file name “C:\Rebuild\RebuildLogDB_log.ldf” may be incorrect.

New log file ‘C:\Rebuild\RebuildLogDB_log.ldf’ was created.

The database would have been created. To test the scenario my DBA friend was saying, I went to SSMS and tried to take a Transaction Log Backup. But was surprised we didn’t have the option.

The catch here is hidden in the “Recovery Model” that is shown above. If you notice, it is showing as “SIMPLE” – which means we willnot be able to take a TLog backup without changing the same.

The resolution for this is simple, change the recovery model to FULL and take a Full backup for TLog backups to succeed now.

Once we do this, everything will come back to normal status. This was an example to show you what happens when we rebuild the Log without knowing it changes the Recovery Model to Simple in the process.

PS: Please DONOT do this in your production environment.

Reference: Pinal Dave (https://blog.sqlauthority.com)