February 22, 2011 at 6:55 am
Hello gents
It is known that from table msdb.dbo.backupset you can retrieve the info about all the successful backups and where to find the info about all the failed backup attempts?
I hope that I didn't ask a very stupid question.
Help is always appreciated!
Bazinga!
February 22, 2011 at 9:41 am
i use this for all my jobs
select count(1)
from msdb.dbo.sysjobs sj
left outer join msdb.dbo.sysjobsteps sjs on sj.job_id=sjs.job_id
where sj.enabled=1 and sjs.last_run_outcome=0),0)
February 22, 2011 at 9:43 am
declare @errcnt as int
set @errcnt = isnull((select count(1)
from msdb.dbo.sysjobs sj
left outer join msdb.dbo.sysjobsteps sjs on sj.job_id=sjs.job_id
where sj.enabled=1 and sjs.last_run_outcome=0),0)
if @errcnt =0
begin
exec master.dbo.xp_sendmail @recipients ='missqladmins@nationsholding.com'
,@message ='All Jobs Steps Successful'
,@subject = 'Jobs Steps Successful'
end
else
begin
declare @subject as varchar(1000)
set @subject=rtrim(cast(@errcnt as varchar(50)))+' Job Step(s) That Failed'
exec master.dbo.xp_sendmail @recipients ='missqladmins@nationsholding.com'
,@message ='Jobs Steps That Failed'
,@query = 'select
left(sj.originating_server,15) as Server
,cast(sj.name as varchar(50)) as name
,sjs.step_id
,cast(sjs.step_name as varchar(25)) as step_name
,sjs.last_run_duration
from msdb.dbo.sysjobs sj
left outer join msdb.dbo.sysjobsteps sjs on sj.job_id=sjs.job_id
where sj.enabled=1 and sjs.last_run_outcome=0
order by sj.name'
,@subject = @subject
,@width = 512
end
February 22, 2011 at 9:52 am
Great thanks for your contribution.
But my question is that I want to see who and when tried to backup my databases by using T-SQL command "Backup database..." and failed. It is totally not job-based.
Is there a way to monitor this kind of behaviors from any user accounts in sql server?
Bazinga!
February 22, 2011 at 11:24 am
From default trace, you will find backup information, including Who, when, where and which database.
Go to C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG,
Defaule file name like log_xx.trc.
February 22, 2011 at 11:25 am
Or you can use this sql query to get backup audit,
select * from ::fn_trace_gettable('DefaultTraceFolder\log_601.trc',default)
where textdata like '%backup%' and textdata is not null
February 23, 2011 at 1:42 am
Thanks very much Judy for helping learning some new stuff.
However I just realized that the server I want to trace is version 2000. Is there a similar feature like this in sql server 2000?
Bazinga!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply