Why does my log show two backup events for the same database?

Question

Why does my log show two backup events for the same database?

jasona.work

SSC Guru

Points: 50118
More actions
November 21, 2012 at 1:56 pm

#270426

So looking over logs today, I saw the following:
11/20/2012 6:02:52pm - Backup - Database backed up. Database... Pages dumped: 9218
11/20/2012 7:00:13pm - Backup - Database backed up. Database... Pages dumped: 9370
The first LSN and last LSN for both backups do NOT match.
The first backup event @ 6:02pm, the device is listed as a "Virtual_Device," while the 7pm backup went to disk.
I've checked all Agent jobs, and there is only one backup job, and it's scheduled to run at 6pm.
Finally, to play it safe, I attempted to restore from the backup file (one BAK and several Transaction Log backups from later) and had no problems.
So I don't think this is a problem, but I'm just curious as to what is causing this behavior.
Is the 6pm, which is going to the virtual device, a VSS snapshot, which is then being written into the final backup file?
Thanks,
Jason A.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

TheMaskedDBA SSCrazy Points: 2787 More actions · Answer 1

There is no doubt that the additional backup is occurring. Why dont you profile the backups and see the application name ? Is it possible that another job is kicked off by a windows scheduled task (@ scheduler) or something ? If its a regular occurrence, you only need to profile at the time the non scheduled backup is occurring, to get more color.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 2

I had a similar problem. It turned out to be a job running that would switch the database to SIMPLE to load some tables in the hopes of getting minimal logging and then back to full followed by a full backup to restart the log chain.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

jasona.work (11/21/2012)
So I don't think this is a problem, but I'm just curious as to what is causing this behavior.

Two backup jobs.

If you are running differentials that second one will cause you trouble. If you're just using full and log, there won't be any problems.

Possibly something (like BackupExec) running at the server level and hooking into SQL to take database backups. Speak to your windows admin

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

jasona.work SSC Guru Points: 50118 More actions · Answer 4

GilaMonster (11/22/2012)
jasona.work (11/21/2012)
So I don't think this is a problem, but I'm just curious as to what is causing this behavior.
Two backup jobs.
If you are running differentials that second one will cause you trouble. If you're just using full and log, there won't be any problems.
Possibly something (like BackupExec) running at the server level and hooking into SQL to take database backups. Speak to your windows admin

We don't do Differentials, we've got the time to do one full per-day followed by trans log backups.

Heh. I am the Windows Admin...

:Whistling:

I know we don't have anything like Backup Exec on the box in question, and I recently turned off the Data Protection Manager backups as they seemed to break the SQL backups (could restore the BAK, but none of the Trans Log backups.) I set up a monitoring job yesterday and let it run, maybe I'll pick up some clues from that on Monday.

Thanks everyone!

Jason

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

There is something running a second backup job on that server. The LSNs and times are different, hence it is not the same backup being logged twice. VDI means it's not a backup to file run from within SQL, it's some 3rd party backup tool (windows backup, backupexec, litespeed, SQLBackup, etc, etc)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

jasona.work SSC Guru Points: 50118 More actions · Answer 6

Grrr. I started my capture a little too late on Thursday, now I need to try again tonight...

jasona.work SSC Guru Points: 50118 More actions · Answer 7

So, the trace / profile ran without a hitch last night. The "extra" backup job is being kicked off by the SQL VSS writer. What I *THINK* may be causing this is our Data Protection Manager system. The DPM is configured to backup some of the DBs on this particular server (all the ones in Simple Recovery [Master, MSDB, Model]) and some WSS DBs from Team Foundation Server (which are in Full Recovery)

I removed the WSS DBs from the DPM schedule, and added them to my Agent backup jobs (DB and TLog backups,) so I'll see what happens tonight with this.

Thanks everyone!

Jason