SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
jasona.work
jasona.work
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18002 Visits: 13363
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.
sqlGDBA
sqlGDBA
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1034 Visits: 338
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
Jeff Moden
SSC Guru
SSC Guru (377K reputation)SSC Guru (377K reputation)SSC Guru (377K reputation)SSC Guru (377K reputation)SSC Guru (377K reputation)SSC Guru (377K reputation)SSC Guru (377K reputation)SSC Guru (377K reputation)

Group: General Forum Members
Points: 377676 Visits: 42923
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
GilaMonster
GilaMonster
SSC Guru
SSC Guru (412K reputation)SSC Guru (412K reputation)SSC Guru (412K reputation)SSC Guru (412K reputation)SSC Guru (412K reputation)SSC Guru (412K reputation)SSC Guru (412K reputation)SSC Guru (412K reputation)

Group: General Forum Members
Points: 412072 Visits: 47117
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
jasona.work
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18002 Visits: 13363
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (412K reputation)SSC Guru (412K reputation)SSC Guru (412K reputation)SSC Guru (412K reputation)SSC Guru (412K reputation)SSC Guru (412K reputation)SSC Guru (412K reputation)SSC Guru (412K reputation)

Group: General Forum Members
Points: 412072 Visits: 47117
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
jasona.work
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18002 Visits: 13363
Grrr. I started my capture a little too late on Thursday, now I need to try again tonight...
jasona.work
jasona.work
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18002 Visits: 13363
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search