Do SQL Backup Jobs Affect TDP SQL?

[m*a*s*h]

I do weekly fulls, daily differentials and hourly log backups through TDP SQL.

What affect would a SQL job doing periodic fulls have on the backups taken through TDP SQL? The DBA's here sometimes take random backups of the database. I wasn't sure if this would have a negative affect on my backups.

Thanks

 

[moon-buddy]

Why? This seems redundant. Yes, it may have some effect.

 

[m*a*s*h]

Because they are crazy and I can't physically stop them.

 

[GregE]

You may have a problem IF you take SQL log backups for sure, and maybe even full. I can't remember it all now but I had my problem documented for my own info later. You're not taking Log backups specifically, but a full backup backs up the logs to make the full backup consistent. We were not hurt by it because we never needed a production restore, but if we had, we could have potentially had a mess. You're in the same situation.

Example....
As I was using TSM TDP restoring to a point in time, using FULL + several LOGs, I would get the following error:

The log in this backup set begins at LSN 935366000000004700001, which is too recent to apply to the database. An earlier log backup that includes LSN 935360000000018300001 can be restored.

I tried a couple of databases with different points in time, and each time I would get the same error. Using TDP, if I only restored the FULL, no problems. An internet forum post regarding this error mentioned the possibility of a different process having done a transaction log backup between their planned log backups, and that that could create a "break" in the trans log sequence that was keeping their restore from being successful. They could restore some of the logs, but not all of them. In our case, the "other" process was the SQL backup.

Our situation was this.......we backup TDP FULL at 23:00, not doing the first subsequent TDP LOG backup until 07:00. But, in between, SQL LOG backup runs at 00:00 and again at 06:00 (and FULL at 01:40). This was causing the break in the sequence TDP is expecting, and the error we saw when restoring. TDP knows about the FULL it takes at 23:00 and the LOG it takes at 07:00, but isn't aware of the SQL LOG backup at 00:00 and 06:00, so the first log TDP takes at 07:00 is, as the error says when restoring, "too recent to apply to the database" because a prior one was taken not by TDP, but by SQL, which affects the sequence.

I can't remember now, or if I even tested at the time, if SQL FULL backup would cause the same problem. It's possible, because an SQL FULL backup, to be consistent, backs up transaction logs too, I think.

Moral is, though you are not using SQL to backup logs, test a FULL+LOGS restore, using TDP, and try applying logs from after the time of your SQL full backup.

 

[m*a*s*h]

What I was afraid of possibly happening is exactly what you described. My only suggestion to them was if they must take a separate full backup to do so within a few hours prior to my full backup.

Thank you for taking the time to reply.