Backup related questions can sometimes be challenging, especially when it comes to understanding the status of bulk logging operations in SQL Server. In a recent conversation with a DBA friend, he asked me how to determine if a bulk logging operation was performed on his SQL Server. He explained that there were several SSIS jobs running throughout the day and night, and he wanted to identify which log backups contained the bulk logged operation.

While there are various audit settings that can be implemented to capture this information, there is a simpler way to identify the logs with bulk logged operations – by reading the SQL Server logs themselves.

Here is a step-by-step guide on how to find the status of bulk logging in SQL Server logs:

1. Create a test database:

  CREATE DATABASE MyBulkDB
  GO
  

2. Change the recovery model to bulk logged:

  USE [master]
  GO
  ALTER DATABASE MyBulkDB SET RECOVERY BULK_LOGGED WITH NO_WAIT
  GO
  

3. Perform a minimally logged operation:

  USE MyBulkDB
  GO
  CREATE TABLE test (id int)
  GO
  INSERT INTO test values (1)
  GO
  SELECT * INTO test2 FROM test
  GO
  

4. Revert back to full recovery model:

  USE [master]
  GO
  ALTER DATABASE MyBulkDB SET RECOVERY FULL WITH NO_WAIT
  GO
  

5. Take a log backup:

  BACKUP LOG [MyBulkDB] TO DISK = N'C:\Data\Bulk1.bak' 
  WITH NOFORMAT, NOINIT,  NAME = N'MyBulkDB-Log Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
  GO
  

6. Read the backup file headers:

  RESTORE HEADERONLY FROM DISK = N'C:\Data\Bulk1.bak' WITH NOUNLOAD;
  GO
  

When reading the backup file headers, you will notice a column named “HasBulkLoggedData”. If this column is present and set to “1”, it indicates that the log backup contains operations that were minimally logged, even though the database is in the full recovery model.

By utilizing this simple method, you can easily identify which log backups contain bulk logged operations in your SQL Server environment. It’s important to explore and know where to find the information you need, as it can greatly enhance your understanding of SQL Server.

Have you ever encountered a similar situation where you needed to determine the status of bulk logging operations? Share your experiences and let’s learn and grow together!