Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Backup failure History....


Backup failure History....

Author
Message
Sgar...
Sgar...
SSC-Enthusiastic
SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)

Group: General Forum Members
Points: 179 Visits: 666
Hi Team,


I need to find out the backup failure history.....

Till date how many backup are failed in sql server how can I find that...

please help...

Sagar Sonawane
** Every DBA has his day!!Cool
Tava
Tava
SSC-Enthusiastic
SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)

Group: General Forum Members
Points: 156 Visits: 774
Saga... (12/28/2011)
Hi Team,


I need to find out the backup failure history.....

Till date how many backup are failed in sql server how can I find that...

please help...


Could be wrong here but if I get what you're after your backup should be hopefully setup as a maintaince task/SQL Agent Job.

If you right click on the task itself and select "View History" it will give you the information you require i.e. Date, Plan Name,Duration, Error Number & Error Message (plus more). You can then investigate that error message in full.

Hopefully that's what you were trying to get as an answer - if not expand more.
Sgar...
Sgar...
SSC-Enthusiastic
SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)

Group: General Forum Members
Points: 179 Visits: 666
Thanks TAVA for reply.

Actually I was seeking information for the failed backups in SQL Server. (Those backups which are not successful. We can notify it in error log, job history. Is there any other way??)

Sagar Sonawane
** Every DBA has his day!!Cool
Tava
Tava
SSC-Enthusiastic
SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)

Group: General Forum Members
Points: 156 Visits: 774
Saga... (12/28/2011)
Thanks TAVA for reply.

Actually I was seeking information for the failed backups in SQL Server. (Those backups which are not successful. We can notify it in error log, job history. Is there any other way??)


I'm not to sure other than Job History, As i have never needed to look at other alternatives. You can have alerts created if you're using "Maintenance Plans" to notify you upon an unsuccessful backup but this would be for new backups not previously failed ones priori.
Tava
Tava
SSC-Enthusiastic
SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)

Group: General Forum Members
Points: 156 Visits: 774
Tava (12/28/2011)
Saga... (12/28/2011)
Thanks TAVA for reply.

Actually I was seeking information for the failed backups in SQL Server. (Those backups which are not successful. We can notify it in error log, job history. Is there any other way??)


I'm not to sure other than Job History, As i have never needed to look at other alternatives. You can have alerts created if you're using "Maintenance Plans" to notify you upon an unsuccessful backup but this would be for new backups not previously failed ones priori.



Just to add, not sure if this covers Unsuccessful backups or not but saw this article on MSDN website. http://msdn.microsoft.com/en-us/library/ms188653.aspx "A complete history of all SQL Server backup and restore operations on a server instance is stored in the msdb database"
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21101 Visits: 18259
Use the query here to find your failed jobs.

http://sqlserverpedia.com/wiki/SQL_Server_Agent_Job_Query_Samples

To query backups for a database, you can query the backupset (and backup tables) in the msdb database.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Sgar...
Sgar...
SSC-Enthusiastic
SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)

Group: General Forum Members
Points: 179 Visits: 666
As I found query for backup history from msdb..backupset

select database_name,backup_start_date,backup_finish_date,type, backup_size from msdb.dbo.backupset WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 30)
ORDER BY
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_finish_date




I didn't found any query which will retrieve failed backup information...

Sagar Sonawane
** Every DBA has his day!!Cool
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21101 Visits: 18259
Hopefully you will have the backups scheduled through an agent job. If you do, you can report on job history to find if a backup job completed successfully or not.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Sgar...
Sgar...
SSC-Enthusiastic
SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)

Group: General Forum Members
Points: 179 Visits: 666
I have scheduled Jobs for SQL backups. for daily, weekly and monthly.

Found last months backup output as 54217 rows.

manually its not possible to check job history.


Can you suggest any query for failed jobs. (query provided in http://sqlserverpedia.com/wiki/SQL_Server_Agent_Job_Query_Samples#Query_Test_Checklist)

is very lengthy... will make overhead on server.

Sagar Sonawane
** Every DBA has his day!!Cool
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21101 Visits: 18259
This query is fairly quick. If you need to know the success/failure of your jobs and you have that much history to comb, you will need to run a query such as this.


SET NOCOUNT ON

DECLARE @MaxLength INT
SET @MaxLength = 50

DECLARE @xp_results TABLE (
job_id uniqueidentifier NOT NULL,
last_run_date nvarchar (20) NOT NULL,
last_run_time nvarchar (20) NOT NULL,
next_run_date nvarchar (20) NOT NULL,
next_run_time nvarchar (20) NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL,
request_source INT NOT NULL,
request_source_id sysname
COLLATE database_default NULL,
running INT NOT NULL,
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL
)

DECLARE @job_owner sysname

DECLARE @is_sysadmin INT
SET @is_sysadmin = isnull (is_srvrolemember ('sysadmin'), 0)
SET @job_owner = suser_sname ()

INSERT INTO @xp_results
EXECUTE sys.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner

UPDATE @xp_results
SET last_run_time = right ('000000' + last_run_time, 6),
next_run_time = right ('000000' + next_run_time, 6)

SELECT j.name AS JobName,
j.enabled AS Enabled,
CASE x.running
WHEN 1
THEN
'Running'
ELSE
CASE h.run_status
WHEN 2 THEN 'Inactive'
WHEN 4 THEN 'Inactive'
ELSE 'Completed'
END
END
AS CurrentStatus,
coalesce (x.current_step, 0) AS CurrentStepNbr,
CASE
WHEN x.last_run_date > 0
THEN
convert (datetime,
substring (x.last_run_date, 1, 4)
+ '-'
+ substring (x.last_run_date, 5, 2)
+ '-'
+ substring (x.last_run_date, 7, 2)
+ ' '
+ substring (x.last_run_time, 1, 2)
+ ':'
+ substring (x.last_run_time, 3, 2)
+ ':'
+ substring (x.last_run_time, 5, 2)
+ '.000',
121
)
ELSE
NULL
END
AS LastRunTime,
CASE h.run_status
WHEN 0 THEN 'Fail'
WHEN 1 THEN 'Success'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Cancel'
WHEN 4 THEN 'In progress'
END
AS LastRunOutcome,
CASE
WHEN h.run_duration > 0
THEN
(h.run_duration / 1000000) * (3600 * 24)
+ (h.run_duration / 10000 % 100) * 3600
+ (h.run_duration / 100 % 100) * 60
+ (h.run_duration % 100)
ELSE
NULL
END
AS LastRunDuration
FROM @xp_results x
LEFT JOIN
msdb.dbo.sysjobs j
ON x.job_id = j.job_id
LEFT OUTER JOIN
msdb.dbo.syscategories c
ON j.category_id = c.category_id
LEFT OUTER JOIN
msdb.dbo.sysjobhistory h
ON x.job_id = h.job_id
AND x.last_run_date = h.run_date
AND x.last_run_time = h.run_time
AND h.step_id = 0






Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

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