Yoink – Backups Gone
Not only is this mystery of missing backup files worth discussing now, but has been a mystery worth talking about previously (albeit in slightly different form). One such exploration into this mystery can be read here. And as a requirement for exploring any mystery some tools are necessary to be able to explore the clues that might be revealed. For this mystery, we will be utilizing this script along with our acutely developed sense of perception, ability to fact-find and our ability to troubleshoot.
Without further ado and all of our tools in hand, let’s begin!
Peter Deebweehay and Tommy Tsisamin work at Global Exchange Wholesale Retailers (any similarity to real entities is wholly unintentional and completely fictitious). Both Peter and Tommy are diligent hard working individuals. Peter happens to be a DBA and is stubborn about ensuring databases are backed up regularly. He has carefully planned out the disaster recovery scenarios and has nit-pickingly designed the backup strategy to ensure business continuity.
Tommy on the other hand is just as head-strong about performing his daily duties as the systems administrator. Tommy is responsible for the virtual machines, network infrastructure, operating systems, domain, mail services and desktop machines (OS to hardware). Tommy has also carefully planned out all of the disaster recovery needs of the business for all of the servers and specific workstations of individuals that might be pertinent to the continuity of the business.
Tommy and Peter get along together glowingly. The two work together any time there is a new SQL Server to be stood up and have worked together previously many times to ensure good performance of the SQL Servers on both physical and virtual machines.
Lately users have been complaining about a performance slowdown. The slowdown passes quickly but it keeps coming back. Peter starts to look into the reports and is slowly developing a pattern. He notices that the slowdown occurs every hour. Like a good DBA, Peter decides to implement an Extended Event session to capture the queries and server activity that occurs for a period of 3 hours to hopefully capture data that could indicate what the offender is.
After the three hour window has passed, Peter pulls out the captured data and starts looking it over. He notices that every hour there is a backup that occurs against this database. Peter knows that shouldn’t be happening because it is out of cycle with what he had implemented. To be absolutely certain, Peter decides to check the SQL Agent jobs and the Windows task scheduler. Nothing indicating a backup in either location. Next Peter pulls out his trusty backup history script to confirm the data he pulled from the XE session data. When he starts looking at the data, his jaw drops as he sees something that looks like the following.
Looking at these results, Peter starts looking in the Backup share he had created for these backups and can’t find anything representing those backups with the GUIDs in the BackupPath result. Then he ponders for a minute. Peter knows he has seen something like this before and then recalls where he saw it. Those Full backups with the GUID as the path are coming from outside of SQL Server.
Peter is curious if Tommy could possibly help with the new-found mystery on this server. When Peter presents the information to Tommy, Peter is met with another jaw-dropping moment. Tommy, with a wry smile and pride in himself, explains to Peter exactly what those GUIDs happen to be (not knowing the connection). Tommy explains that he has set the virtual machine to perform an incremental backup every hour. This incremental backup is represented to SQL Server as a Full database backup and produces a backup that equates to .005MB. With this data, Peter is curious to know if Tommy is quiescing the database(s) prior to doing this incremental backup. As it turns out, Tommy was not. Peter was mildly frustrated but he was able to work out a better solution with Tommy. Without quiescing the database, these full backups become questionable as to the usability to actually recover this server (an incremental OS backup is not the same as a Database Differential backup).
As far as the database is concerned, these GUID backups are much the same as a missing backup. Any backup that is taken that a DBA cannot explain or find would also be a missing backup.
What does this mean for you?
In the case of Peter and Tommy, this meant that there was a bit of a mis-communication and a bit of a performance hit for these off cycle backups. Had Peter implemented differential backups, the recovery plan he had implemented would be for naught because each of those full backups caused by the incremental backup for the VM would break his differential chain. Also, if Peter had attempted to recover the database to a different server, he might have discovered this sooner and before the users had reported the performance issues that led him to the discovery. Not only is a backup plan essential, but monitoring of the logs and backups as well as routine practice restores is necessary.
After reading this article, enjoy more articles similar in nature such as this series on Extended Events or this series called “Back to Basics“
