Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Backup failure History.... Expand / Collapse
Author
Message
Posted Wednesday, December 28, 2011 7:44 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, June 25, 2014 6:20 AM
Points: 159, Visits: 622
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!!
Post #1227619
Posted Wednesday, December 28, 2011 8:20 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, April 27, 2014 8:38 PM
Points: 90, Visits: 420
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.

Post #1227623
Posted Wednesday, December 28, 2011 9:14 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, June 25, 2014 6:20 AM
Points: 159, Visits: 622
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!!
Post #1227629
Posted Wednesday, December 28, 2011 9:22 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, April 27, 2014 8:38 PM
Points: 90, Visits: 420
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.



Post #1227630
Posted Wednesday, December 28, 2011 9:33 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, April 27, 2014 8:38 PM
Points: 90, Visits: 420
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"
Post #1227632
Posted Wednesday, December 28, 2011 10:34 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 1:51 PM
Points: 21,644, Visits: 15,317
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1227641
Posted Thursday, December 29, 2011 10:22 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, June 25, 2014 6:20 AM
Points: 159, Visits: 622
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!!
Post #1228168
Posted Thursday, December 29, 2011 10:24 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 1:51 PM
Points: 21,644, Visits: 15,317
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1228169
Posted Thursday, December 29, 2011 10:32 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, June 25, 2014 6:20 AM
Points: 159, Visits: 622
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!!
Post #1228171
Posted Thursday, December 29, 2011 10:38 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 1:51 PM
Points: 21,644, Visits: 15,317
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1228174
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse