Sounds scary, huh? And it surely can be! Here’s an example of one such case:
A client of mine had implemented a 3:rd party backup solution in their VM Ware environment. This shouldn’t affect the SQL Server backups. Or that was the plan. I won’t mention what backup software this is, since I’m sure that there are other software also doing strange things. And in the end, it is up to you to verify that your backup and restore strategy is solid. So let us just call this software “BACK”.
BACK performs a snapshot backup of the virtual machine. The client had scheduled this so it happened at about 4 am each day.
Our SQL Server backups were implements so that 7 pm we did a full backup and every hour we did a log backup.
Nothing strange here and these should be independent of each other, right? Wrong!
When looking at the backup history, I realized that SQL Server would see the snapshot backup as a full backup. OK, that will possibly affect out plan to go for differential backups, but it shouldn’t affect our transaction log backups. We all know that a database backup doesn’t break or affect the log backup chain.
But what I did find was that BACKUP performed a log backup immediately after the snapshot database backup. And the log backup was taken to the file name “nul”. Yes, the binary wasteland.
The end result of this was that the log backups that we performed were usable between 7 pm and 4 am. After that, the log backups are useless. No option to restore anything for the work performed during the working day. It would have been more honest if BACK would set the recovery model to simple instead. That would at least give us a more fair chance to catch the problem (sooner then we did).
Now, we did find an option in BACK to not truncate the log (or whatever they called the checkbox), but by default it did perform this disastrous log backup to nul.
The next step was to consider implementation of the differential backup plan. But time was running out for my assignment so we only managed a quick check. And from what we found out, it seemed that BACK doesn’t have an option to produce its snapshot backup to be seen as a COPY_ONLY backup. This means that it prohibits us to implements SQL Server differential backup and saving some 500 GB backup storage produced each week (for only one of the servers). Now, let me leave a disclaimer here since I didn’t have time to investigate this much as all, but this is what it looks like at the moment. I will dig deeper into the differential backup strategy next time I’m with this client.
The moral of the story? You already know that. Test your restore strategy. You might just be (unpleasantly) surprised!