Backup related questions are always challenging and sometimes our basics is shaken when someone randomly asks us something we were not aware of. Sometimes it takes a little bit of digging around in documentation and we are in for a surprise because this has been there for quite some time. In a recent conversation with a DBA friend of mine in a consulting exercise, he came back to me with an interesting question. Let us see in this blog post about how to find the status of Bulk Logging Enabled or not from SQL logs.
Pinal, how do I know if a Bulk Logging operation was performed on my SQL Server. I know there are a number of SSIS jobs that run during the day and night and I have a number of log backups that are taken continuously. Some of these jobs actually change the recovery model to Bulk-Logged and end of the process, they turn it back to Full once the script was to end. I know I can do a number of audit settings to make sure this is captured. But is there any other way to identify which of these logs when taken have the Bulk logged operation?
I was amazed with the simple requirement and there is enough literature on MSDN to tell the pro-cons of doing this and what are all the pitfalls. From an operations guy, this DBA’s question was amazing and very valid. He is asking me to read from the Logs.
So I created a simple test to find out how this can be figured out. The steps for the code is simple:
- Create our Database
- Change the model to Bulk
- Do a minimally logged operation
- Take a log backup
- Read the headers and finally
- Clean up
Our T-SQL Script would be:
CREATE DATABASE MyBulkDB GO BACKUP DATABASE MyBulkDB TO DISK = N'nul' GO -- Let us change the Recovery model to Bulk USE [master] GO ALTER DATABASE MyBulkDB SET RECOVERY BULK_LOGGED WITH NO_WAIT GO
The next step is to create something and do an BULK operation. I am using a simple SELECT INTO command to generate a minimally logged operation.
-- Add some table and a Bulk operation, here SELECT INTO USE MyBulkDB GO CREATE TABLE test (id int) GO INSERT INTO test values (1) GO SELECT * INTO test2 FROM test GO
Now we will revert back to FULL before our backup.
-- Revert back to FULL recovery model USE [master] GO ALTER DATABASE MyBulkDB SET RECOVERY FULL WITH NO_WAIT GO
Let us take the Log backup and read the headers to see what is available inside the log files.
-- 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
-- Read our backup file RESTORE HEADERONLY FROM DISK = N'C:\Data\Bulk1.bak' WITH NOUNLOAD; GO
I am sure you have used this command previously too, and the most interesting part here is to find the column that is named as “HasBulkLoggedData”. This shows us that the current log backup now contains operations that are minimally logged, even though the database is now in the full recovery model. I felt this was a hidden gem and quite useful for this user.
-- Clean up time USE master GO DROP DATABASE MyBulkDB GO
Many a times the data that we are expecting and searching is already there, we need to explore and must know where to find them. This learning has made me read and watch SQL Server even more under the microscope. I think the best way to learn is by asking the right questions. We learn and can grow together if we can share some of these interesting problems. Have you ever wanted to know such information in your environment? Do let me know.
Reference: Pinal Dave (https://blog.sqlauthority.com)