As a SQL Server database administrator, it is important to understand the concept of transaction log backups and how they play a crucial role in maintaining the integrity and recoverability of your databases. In this blog post, we will discuss a common issue related to transaction log backups and how to resolve it.

Recently, I had a conversation with a fellow DBA who was facing a problem with transaction log backups. He approached me during a conference and explained that their transaction log backups were failing, but there was no specific error message. After some investigation, I discovered that the issue was related to a recent hard disk corruption incident.

When the hard disk crashed, they detached the database and replaced the faulty hard disk. However, when they reattached the database, they unknowingly rebuilt the transaction log. This action changed the recovery model of the database to “Simple,” which prevented them from taking transaction log backups.

To demonstrate this scenario, I have created a step-by-step guide:

Step 1: Create Database and Set Recovery Model

First, create a new database and set the recovery model to “Full.” This can be done using the following SQL script:

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

Step 2: Detach the Database

Next, detach the database and manually delete the transaction log file from the folder. This can be done using the following SQL script:

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 Database without Log File

Finally, attach the database without the transaction log file. This can be done using the following SQL script:

USE [master]
GO
CREATE DATABASE [RebuildLogDB] ON
( FILENAME = N'C:\Rebuild\RebuildLogDB.mdf') 
FOR ATTACH
GO

After executing these steps, you will receive a warning message indicating that the log file is missing. If you try to take a transaction log backup at this point, you will notice that the option is not available. This is because the recovery model has been changed to “Simple.”

To resolve this issue, simply change the recovery model back to “Full” and take a full backup. This can be done using the following SQL script:

USE [RebuildLogDB]
GO
ALTER DATABASE [RebuildLogDB] SET RECOVERY FULL
GO
BACKUP DATABASE [RebuildLogDB] TO DISK = N'NUL'
WITH NOFORMAT, NOINIT, NAME = N'RebuildLogDB-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

Once you have completed these steps, you will be able to take transaction log backups as usual.

It is important to note that this scenario is for demonstration purposes only and should not be performed in a production environment.

Understanding the intricacies of SQL Server transaction log backups is essential for maintaining the recoverability of your databases. By being aware of potential issues, such as the one discussed in this blog post, you can proactively address them and ensure the smooth operation of your SQL Server environment.

Thank you for reading!