Back in August, I discovered an issue that impacted Commvault native dump backups of Amazon RDS SQL Server and will affect all users who are backing up these databases in a time-zone other than UTC+0. This blog goes into some detail about this problem and why you must be careful how you restore your backups. A Diagnostic Fix is currently available on request by Commvault and will soon be mainstream come November 2020 Maintenance Pack, but users need to be aware that this fix will not retrospectively resolve your past Amazon RDS SQL Server backups.
How to reproduce the issue
The steps described here are the for the Commvault CommCell Console, but the reproduction steps are also just as relevant to the Command Center.
Attempt a Browse and Restore by Job
Click View Content
Error: There is no data to restore. Verify that the correct dates have been entered.
So what happens if you really need to restore that backup and you browse and restore by Time Range?
Our first job backup here finished at 7:44:52PM on the 26th August, 2020 and I have chosen a restore by End Time that is 9:59 ahead of when the backup occurred (9:59 = +10 hours – 1 minute).
However if I repeat the Browse and Restore by End Time, but choose a time 10:01 ahead of the backup
And voila, Commvault was able to retrieve the list of SQL Server database backups from the Job!
And the problem is?
The “There is no data to restore. Verify that the correct dates have been entered” error only appears if there are no backups on or before the Browse by Date Time. Whenever you have an error message come up, you clearly know that you have to make corrective action. However, the problem here is that when you browse and restore this way, it is quite likely that you will either restore an older or newer backup; and the backup operator will not even know until the DBA discovers the error.
So a restore by Date and Time, requires the Backup Operator to do a time calculation. For many customers that work in a single time zone, this may be quite straight forward. However extra care must be taken when restoring databases that could be in a different zones.
The Good, the Bad and the Ugly
The good news is that there is a Diagnostic Hotfix from Commvault that needs to be installed on the Access Node, and I can confirm that it was prepared for at least Commvault FR11.20.17 and for FR11.21.5. Contact Commvault support if you just want this Diagnostic Fix, or install the November 2020 Maintenance Pack across your CommCell. This will re-enable you to do a Browse and Restore by Job without having to browse by date.
The bad is that it does not retrospectively fix the job history. Why? Sadly it is just too risky for Commvault to create an Update script to the sqlDBBackupInfo table to update the Database Backup times to reflect the true timestamp because there is no safe way to do it globally for all time-zones.
The ugly means your backup operators need to be aware when this patch is applied so they know which dates a Browse and Restore by Job will work and also when they must restore by providing a date range as described in this blog.