Recently, I was alerted to a network file share that was running low on disk space. This interesting as there was only one process that used this particular location to store SQL Backups. We have a nightly job that runs full copy only backups of a couple critical databases that are scrubbed and restored to a development environment each day.
This process had been running smoothly for some time now. So, it was odd that the backup files have filled up the backup location.
Investigation
I put on my investigator hat and begin looking around. I started with the Windows File Server to see what was actually on the drive in question. Just as I thought, three SQL backup files were in there proper folder. Although there were only three files, something else caught my attention. One backup file was 800GB and another 1TB. That was strange as I don’t think the source databases are that big. Sure enough, I look and one database is 60GB and the other is 45GB.
Something is not right here!! So, next, I run a
RESTORE HEADERONLY against one of the backup files. What did I see? 32 files contained within the one backup file. This is not right, the file should be overwritten each day the backup job executes.
About a month ago, I modified the step of this SQL Agent Job to use the Ola Hallengren backup commands because that was our standard across the environments. I like his tools because they are easy to configure and easy to scale across a lot of servers. Plus, I have made enhancements to allow for centrally managing the settings for all jobs on all servers. 
Let’s take a look at the actual code that is executed for this job step.
EXECUTE [dbo].[DatabaseBackup]
@Databases = 'WideWorldImporters',
@Directory = N'C:Backups',
@BackupType = 'FULL',
@Verify = 'Y',
@Compress = 'Y',
@CopyOnly = 'Y',
@CheckSum = 'Y',
@LogToTable = 'Y',
@DirectoryStructure = NULL,
@FileName = '{ServerName}${InstanceName}_{DatabaseName}.{FileExtension}',
@Execute = 'Y'What I did to standardize this job was to take the nasty looking incorrect TSQL code out of the job and replace with this code. The only unique thing about this process is that the DevOps team needs the same filename generated each time. This allows their automation to look for and grab the file each day and do it’s magic. I typically do not deal with the same filename and so in lies the fact that my change caused this bug to arise.
By default, the Ola backup solution does not perform an INIT or FORMAT when creating a database backup.
This would make sense as the backup solution was appending the backup files each day the job executed and thus growing the backup file on the network share. Normally, we let the backup job code build the filename which includes the date and time in the name.
Testing the theory
After discovering from the Ola website that the default behavior went against what I was trying to do I added the two parameters to the code in my job step and executed a test.
With INIT and FORMAT disabled, you can see how the backups grow and append to the one file.
Now, when I remove the comments from the TSQL code you can see just one backup is in the file at any given time.
Conclusion
When working with SQL backups and making changes to the output, keep a close eye on your process to ensure you are getting the desired results.
For the INIT and FORMAT settings, view the links for more information from Microsoft on their functionality. These is helpful knowledge to expand your knowledge into the internals of database backups.