Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have some SQL 2005 jobs which run nightly backups of the database to a local drive.

The jobs are running OK (Well, I can see that they are doing something) because I can see a new backup file being written each evening.

When I right click on the the Job and view history there is nothing.

Other jobs do display history, and generally speaking there is history older than the job I'm interested in (i.e. the log is not getting cleared down).

Unless the backup is being run by another process (which therefore WOULD have some history) - which I do not believe it is - where is my history?

I think I'm missing something really simple here (it is a Friday afternoon).

share|improve this question
    
Thanks for rubbing in that it's Friday afternoon... Only Friday morning here! (haha) Anyway, is this a job created by a maintenance plan schedule or a job that you created manually? Also, are you using native SQL backup or a third party backup package? –  squillman Aug 28 '09 at 15:44
add comment

2 Answers

Here are some suggestions:

  1. Look at the creation time of the files to see exactly when the job is starting.
  2. Look at the SQL Error Logs. That should give the best indication of what is executing the backup (if it is being initiated by anything controlled by SQL Server, such as another job).
  3. Look at the system's Application log, which would also give some clues.
  4. It could be that the backup for your database is being run by a catchall job (other than the one you set up specifically for your database).
  5. Look at the backup history of the database itself (i.e. not the job history), as that may also present a clue.

I hope that helps (there may well be other places I've left out, but by the time you've gone through all of the above, I'm pretty sure you will have figured it out).

share|improve this answer
add comment

Check out the contents of the msdb..backupset table in msdb for information on the most recent backup. It contains a backup_finish_date field. (This is where information is pulled from when you right click on a database and view the last backup date in the properties.)

select *
from msdb..backupset 
where 
b1.[type] = ('D') -- database backup (not log!)

To figure out what job may be backing up the server, you could try something like this (hopefully this parses, I'm not able to check it right now):

select jobname=j.name
, js.step_id
, js.step_name
, js.command
from msdb..sysjobs j
join msdb..sysjobsteps js on
j.job_id=js.job_id
where js.command like '%backup%'

Here is a blog post I did on checking the last backup date...

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.