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”.
performs a snapshot backup of the virtual machine. The client had scheduled
this so it happened at about 4 am each day.
Server backups were implements so that 7 pm we did a full backup and every hour
we did a log backup.
strange here and these should be independent of each other, right? Wrong!
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
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
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.
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.
of the story? You already know that. Test your restore strategy. You might just
be (unpleasantly) surprised!