Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
cipriani1984
Constraint Violating Yak Guru
304 Posts |
 Posted - 2011-01-20 : 08:31:14
|
| Hi,Im trying to write a query that returns the status of the jobs and backups that happen on the databases on the server.I know this can be done through msdb database, anyone able to provide any guidance for this?Im trying to write a query to get a status on the database backup jobs and their status and times?Thanks in advance. |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-01-20 : 08:36:50
|
| Have a look for tables with job in their namessysjobssysjobhistorysysjobsteps==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
cipriani1984
Constraint Violating Yak Guru
304 Posts |
Posted - 2011-01-20 : 08:45:55
|
I have looked at this and trying to get the database names of the jobs occuring for.in sysjobhistory it contains message, but it shows a long standard message, im just after like 'databasename1'thanksquote:
Originally posted by nigelrivett
Have a look for tables with job in their namessysjobssysjobhistorysysjobsteps==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy.
|
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-01-20 : 08:54:14
|
| The database that the step was executed in is in sysjobsteps.The history of this isn't kept.I usually run a job to copy the job data into another database and keep track of changes.It also helps as the old history in msdb is deleted.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
cipriani1984
Constraint Violating Yak Guru
304 Posts |
Posted - 2011-01-20 : 09:54:05
|
hmmmI basically have a night job setup for backing up the databases, and one tends to fail. I need to write a report for our department to show which successfully ran or failed, ive got a query which works but shows the overall job status.Just trying to get it to work to show each individual database backup worked, but cant seem to get the database name from any of the tables?quote:
Originally posted by nigelrivett
The database that the step was executed in is in sysjobsteps.The history of this isn't kept.I usually run a job to copy the job data into another database and keep track of changes.It also helps as the old history in msdb is deleted.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy.
|
|
|
|
|
|
|
MSDB database