SQL backup error: Unable to get log gap detection data.

There are 2 types of recovery for SQL, I dont remember exactly the names but it is the basic one, that you restore tha backup in the time you took it, and the advanzed where you can restore a point in time.

The error you are getting is because you are selecting the option perform incremental backup after the full backup, and that option is only when you have the advanced recovery option enable in the SQL, just unselect that option and thats it.

The MSDB is using Circular Logging so there is no “starting log” or “ending log”.

David Anderson

Windows System Administrator

Scripps Information Services

Desk: (858) 678-6238

eMail: Anderson.david@scrippshealth.org

"…we live in a world where man has walked on the moon. It wasn't a miracle, we just decided to go". - Jim Lovell, Apollo 13 Astronaut

In 6.1 a new feature is been added to deal with the database which has simple recovery mode.

By default incremental backup cannot be performed on simple recovery database, If force incremental option is selected it will try to perform incremental backup after a full.

Edit the dataset for this backup and choose the option "Skip incremental with warning".

I am also getting these "Unable to get log gap detection data" errors but not just on the msdb database but on all of my databases and all of my databases are set to either "Full" or "Bulk-insert" recovery mode so none of them are "Simple".

The odd thing is that the errors are random meaning one day I get then on all the databases, the next day I get it on may be half of the database, and the number of errored databases bounce up and down each day. 

The one thing I can seem to reproduce everytime is that if I re-run the client via the "Policy" window by selecting the client and clicking "Back Up" it will always fail with the above errors.  But if I do a manual backup via the "Backup and Restore" window and only select the Windows SQL portion of the same client and run the backup with all default settings (just like the default settings in the dataset policy) the backup completes successfully without the errors. 

One other thing I've noticed is that on the databases that give the "Unable to get log gap detection data" the transaction log does not get backed up and therefore can not be truncated until I can do a manual successful backup of that database.

This has been problem from the moment our grid was upgraded from 5.0.2 to 6.1 and I have several SEV 1 tickets open with EMC support and they have engaged their tier 3 support and the developers to figure this out.  It's been 2 weeks and still no resolution other than me doing manual backups everyday.

Ok to give everyone an update and a workaround as the problem is identify that Avamar 6 does NOT like anything touching the SQL databases such as VMware snapshots or any other backup or image base backup that is application aware and uses the SqlServerWriter VSS module or equivalent.  Since I also use VMware's Data Recovery product to do an image base (snapshot) backup of the VMs as an extra layer of protection in case I lose access to my Avamar grid (which is offsite) I had to exclude VMware from ever using SqlServerWriter VSS when it does a snapshot.  This in effect produces a inconsistent SQL database snapshot on VMware side and may be corrupt so restores are not always going to be good but it will at least make Avamar happy and backups complete without the errors.

If your problem are with VMs and you also do any VMware snapshots you can reference VMware KB1031200 as it will tell you how to exclude certain VSS modules when snapshots are made.  Without this workaround EMC Avamar engineering supports response was to NOT create VM snapshots at all which is completely unacceptable as that is one of the main features of VMs.

As far as getting someone to acknowledge this is a problem is going to be futile as EMC Avamar support is now saying is not their problem and it's VMware's problem and VMware support is saying it not their problem as no other backup products have this problem therefore it's an Avamar issue.  You would think two sister companies that are related would communicate and work together, you know what they say about family feuds. This is going to be the longest 'ping pong' game in world history.

The problem stems from the addition of the "Log Gap Detection" feature introduced in Avamar 6.1, this feature basically locks out all other SQL backup products to be used in conjuction with Avamar 6.1.  Avamar's support response is do NOT ever use any other SQL backup products or do any other SQL backups other than with Avamar period.  All other 3rd party SQL backup products aside if you want to do a one off backup of your SQL database using the built-in Microsoft SQL backup that is part of the original Microsoft SQL server installation you CAN NOT according to Avamar support as it will cause a Log Gap Detection error the next time Avamar does it's backup.

If Avamar support/developers were smart they should make the "Log Gap Detection" feature an option via a checkbox like some of the other SQL plugin features.  This way it provides flexiblity for the customers especially those that like to use more than just Avamar to backup their SQL databases.  I suggested this to them and their reply was NO they are not going to do anything nor even consider it case closed!

I just found Avamar's attitude told this to be communistic as after working with them on this for one month their final reply was ONLY use Avamar period and nothing else otherwise it's your problem.  And by the way during the whole one month it was I the customer that troubleshooted and indentified the problem and NOT Avamar support, all they did was insisting I was not doing the Avamar backup right even when I had Avamar support webex in and watch it following their exact instructions.

I've contacted Avamar twice for two different issues and both times I had to give Avamar support a negative rating.  Now on the other hand EMC's Clariion and Celerra support are top notches and I give their support the highest rating as their CS and CE are willing to LISTEN to the customers.  It's like Avamar is not part of the EMC family or it's the step child nobody likes.  Don't get me wrong I love the Avamar product but my two support experiences have left a very sour taste in my mouth.

Hello fl,

I apologize that this has been such a bad experience. 

The Log Gap Detection feature was not added to keep you from using other backup products; it was to keep Avamar from creating bad savesets if you choose to do so.  Prior to Avamar 6.1, a misconfiguration could cause Avamar to report successful backups, when in fact the resulting savesets could not be recovered. 

Microsoft SQL, like most databases, uses a two-stage commit protocol, using transaction logs as the first stage.  SQL Server keeps track of entries in transaction log with LSNs (Logical Sequence Numbers).  SQL Server would need *all* of the transaction entries to do a recovery.  If there is a gap in the chain of logs, a recovery *will* fail.

When a SQL backup is run, depending upon the options selected, log entries are deleted from the SQL transaction log.  This is only done after a backup operation saves the transactions; if a recovery is needed, the backup product recovers the Full saveset, and all of the Incremental and Differential savesets taken since the Full, so the complete chain of transaction logs is available for the recovery. 

But consider: if a *different* backup product does a save, and a portion of the transaction logs has thereby been deleted, then that portion will not be available to Avamar when we next attempt an Incremental or Differential backup; we would have a broken chain.

This new feature has been added to protect DBAs and Backup Admins from that scenario.  The fact that you're seeing this error now suggests that many of the savesets that were being created by the previous version would not have been valid protection of your databases.

I hope this helps,

_Scott

Scott Bingham

Manager, Development Engineering

EMC Avamar

Bellevue, WA

sfbing

We just upgraded and have run into the same thing.  So far, running on-demand full backups with forced-incremental has been working to clean up the errors, ensure that the logs are backed up, and properly truncating the logs.  While good backups are required, truncating the logs to prevent autogrowth and the creation of unnecessary VLF's, or worse, filling up a drive and halting databases is critical for us!

I like the concept of this new feature because it emphasizes the importance of not running backups outside of Avamar, and if you have to, to coordinate the effort to keep our production recovery system (Avamar) consistent.  In practice, when a customer requires a sql bak file, I restore the newest Avamar backup to a dev server, then create the SQL backup over there, never touching the production database for this exact reason.

We also have been experiencing this exact issue – we have a MIS department which prefers to run a scheduled SQL backup and random SQL backups prior to upgrades or changes at will and by doing so they interrupt our Avamar backups with these errors…  We have previously tested out restores understanding any point in time restores would require all points of backup and this has worked flawlessly in 5.x and 6.0, but now at 6.1 Avamar has made this impossible even when trying to use the “COPY_ONLY” option.

Our solution, for now, has been to remain at version 6.1.0-402 which still allows us to use 6.0.101-66 (Client/SQL agent) for only our SQL servers.  All other servers are at this 6.1.0-402 client version, only our SQL servers need to be at this older version of the client/agent…

In discussions with the support team and I guess even one of the higher end developers, we have been informed in order to allow for multiple systems to perform backups (with point in time restores) we will be required to use these alternative methods and use Avamar file level backups to capture these backup files…

Additionally, we use ATO to retain backups for longerperiods rather than using expensive grid space – we also, for now, retain monthly SQL backups indefinitely, by storing this on external encrypted media – it would be nice if there was a way to take advantage of SQL compression so when restoring the DBs out of Avamar for storing onto our external media, a 20 gig file compressed to 2 gig would be terrific.  Ideally, an option box in the SQL dataset to flag SQL compression and storing these f-0 backups into the grid also compressed…

So in order to take full advantage of backup flexibility, when we are forced to upgrade Avamar to SP1 of 6.1, we will be required to either commit to Avamar entirely or switch to SQL file based backups for truncating and point-in-time + take advantage of SQL compression followed by a file-level backup by Avamar.  But for now we are sticking with the older version of Avamar client/agent to allow ourselves exactly what works.

And our DBA indicates the COPY_ONLY should have been a proper avenue to prevent these Gap Detection/Snapup errors; however, even with this in our SQL backups, Avamar continues to ignore and error out regardless.  And just to add, the backups performed by the DBA are simple - non log - COPY_ONLY full backups, no truncating or etc - just a simple file based backup with out point in time restore ability.  Generally taken prior to an upgrade.

Additionally, we use ATO to retain backups for longerperiods rather than using expensive grid space – we also, for now, retain monthly SQL backups indefinitely, by storing this on external encrypted media – it would be nice if there was a way to take advantage of SQL compression so when restoring the DBs out of Avamar for storing onto our external media, a 20 gig file compressed to 2 gig would be terrific.  Ideally, an option box in the SQL dataset to flag SQL compression and storing these f-0 backups into the grid also compressed…

Generally speaking, when we're talking about de-duplicated backups, compression is more likely to hurt than it is to help. Compressed data tends to have very low commonality because two files with large sections of identical data will compress very differently, meaning that practically the entire compressed file will be "new" data.

With regard to exporting the database backups using ATO, it may be possible to compress the database dumps before they are written to the external media but I'm not that familiar with ATO myself. I've sent a message to one of my colleagues to ask if this is possible and I'll let you know what he says. If the external media supports compression, that may be a better option.

or switch to SQL file based backups for truncating and point-in-time + take advantage of SQL compression followed by a file-level backup by Avamar

I would strongly recommend against enabling SQL compression if you move to a "dump and sweep" strategy such as you've described. The low commonality of compressed data may lead to storing large numbers of new chunks on the server and this can cause capacity and GC performance issues on the Avamar server.

The developer I spoke with said the same about compressed backups - which is fine we can compress the f-0 files manually after rehydrating out of Avamar for ATO; however, that is the less important issue to me than this issue about being able to run SQL backups outside of Avamar. If the non-Avamar backup does not touch the logs - uses the COPYONLY attribute and simply dumps a bak file - we have consultants that do this before upgrading etc... and if at any point in time this is done without our knowledge, the entire sequence is interrupted and we have to proceed by running a manual Avamar full followed by the traditional full with forced incremental... It would just be nice if Avamar could ignore the gap detection and allow what is working for us right now.  At least an option to flag in the dataset.

I spoke with the manager of the dev team that works on the SQL plugin and he provided me with a hotfix number that he believes will resolve this issue with COPY_ONLY backups. Hotfix 45717 is built on 6.1.0-402 and hotfix 47948 is built on 6.1.1-87. I would recommend working with support to install this hotfix on one of your systems and testing the COPY_ONLY backups again.

Just to verify, since it has been a while since we ran test point in time restores…

8:49 AM Created DB and Table with Initial Entry
- (1) “Initial Entry”
9:07 AM  Backed up this database with Avamar (6.0.x client)
9:10 AM Updated Initial Entry and also Added new Entry
- (1) “Initial Entry Updated”
- (2) “New Entry”
9:19 AM Performed manual SQL COPY_ONLY backup
9:20 AM Updated Initial Entry and also Added new Entry
- (1) “Initial Entry Updated Again”
- (3) “Last Entry”
9:38 AM Backed up again with Avamar (6.0.x client)
10:19 AM Updated all Entries
- (1) “Initial Entry Updated Again: Final”
- (2) “New Entry: Final”
- (3) “Last Entry: Final”

So, even tho there was a manual backup performed by SQL in between, I was successful in restoring the initial backup by Avamar and using the incremental backup taken at 9:38 to roll forward the database to a point in time prior and also after the manual backup…  So when it is said adding the Gap Detection is to fix a bug in prior to 6.1.x releases, I am a little confused by this since using the 6.0.x client allows me to successfully restore to a point in time regardless if a COPY_ONLY backup was taken.

Now we were actually provided this hotfix previously, however, not sure if we tested it on a backup taken with the COPY_ONLY flagged or not, so I will test that now doing the same process I did for my test with the 6.0.x client.

Restore Process:
- Deleted the new database
- Restored back to the initial Avamar backup taken at 9:07 AM (f-0)
  o “Leave the database non-operational… RESTORE WITH NORECOVERY”
- Continued with a transactional log restore taken at 9:38 AM (i-0)
  o To a point in time: 9:09 AM
  o “Leave the database ready to use… RESTORE WITH RECOVERY”
  o Results, successfully restored prior to manual backup taken with COPY_ONLY
- Redid this above process, but this time to a point in time of: 9:20 AM
  o Results, successfully restored after the manual backup taken with COPY_ONLY
- Proving that using 6.0.x client successfully backs up the database regardless if a manual SQL COPY_ONLY backup was run in-between

Now while at version 6.1.x – COPY_ONLY still reports these Gap Detection errors when backing up by using COPY_ONLY in a manual SQL backup.  Again, I will give it a shot with the hotfix, but the last attempt with this hotfix was not successful.

As it did previously, after upgrading to 6.1.0-402 and applying that hotfix we still receive the following error:

2013-03-21 14:04:45 avsql Error <15762>: Skipping incremental backup after full for (local)/DJC_Trial database. Error: Last full backup time on the SQL server does not match any snapups.

This exact process works under 6.0.x - before/after/whenever I want - I can get the data to any point in time using two Avamar backups regardless of COPY_ONLY backups performed inbetween.  But 6.1.0-402 and also with the mentioned hotfix - still problems...

I did a Full backup (Forced Incremental Off) initially because with this new client this is how it is explained it has to be done - Full (Forced Incremental Off)

- Full with Forced Incremental On (Completed successfully)

- Manual SQL COPY_ONLY performed identically as done in v6.0.x

- Full with Forced Incremental On (Failed with above error) - logs not backed up and also not truncated...

Hotfix -

ftp://avamar_ftp:anonymous@ftp.avamar.com/software/hotfixes/45717/WIN32anonymous@ftp.avamar.com/software/hotfixes/45717/WIN32

ftp://avamar_ftp:anonymous@ftp.avamar.com/software/hotfixes/45717/WIN64anonymous@ftp.avamar.com/software/hotfixes/45717/WIN64

Simply replacing the avsql.exe file does not appear to help us out.