December 28, 2011 at 7:44 pm
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:
December 28, 2011 at 8:20 pm
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.
December 28, 2011 at 9:14 pm
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:
December 28, 2011 at 9:22 pm
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.
December 28, 2011 at 9:33 pm
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"
December 28, 2011 at 10:34 pm
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[/url]
Learn Extended Events
December 29, 2011 at 10:22 pm
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:
December 29, 2011 at 10:24 pm
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[/url]
Learn Extended Events
December 29, 2011 at 10:32 pm
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:
December 29, 2011 at 10:38 pm
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[/url]
Learn Extended Events
December 30, 2011 at 3:24 pm
You can check out from here also
« SQL SERVER – Recycle Error Log – Create New Log file without Server RestartSQLAuthority News – SQL Server Denali CTP1 – Release Date November 9, 2010 »
SQL SERVER – Get Database Backup History for a Single Database
November 10, 2010 by pinaldave
I recently wrote article SQL SERVER – Finding Last Backup Time for All Database and requested blog readers to respond with their own script which they use it Database Backup.
Here is the script suggested by SQL Expert aasim abdullah, who has written excellent script which goes back and retrieves the history of any single database.
USE AdventureWorks
GO
-- Get Backup History for required database
SELECT TOP 100
s.database_name,
m.physical_device_name,
CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize,
CAST(DATEDIFF(second, s.backup_start_date,
s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken,
s.backup_start_date,
CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn,
CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn,
CASE s.[type]
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END AS BackupType,
s.server_name,
s.recovery_model
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE s.database_name = DB_NAME() -- Remove this line for all the database
ORDER BY backup_start_date DESC, backup_finish_date
GO
April 16, 2013 at 6:56 am
logicinside22 (12/30/2011)
« SQL SERVER – Recycle Error Log – Create New Log file without Server RestartSQLAuthority News – SQL Server Denali CTP1 – Release Date November 9, 2010 »
SQL SERVER – Get Database Backup History for a Single Database
November 10, 2010 by pinaldave
I recently wrote article SQL SERVER – Finding Last Backup Time for All Database and requested blog readers to respond with their own script which they use it Database Backup.
Here is the script suggested by SQL Expert aasim abdullah, who has written excellent script which goes back and retrieves the history of any single database.
USE AdventureWorks
GO
-- Get Backup History for required database
SELECT TOP 100
s.database_name,
m.physical_device_name,
CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize,
CAST(DATEDIFF(second, s.backup_start_date,
s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken,
s.backup_start_date,
CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn,
CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn,
CASE s.[type]
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END AS BackupType,
s.server_name,
s.recovery_model
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE s.database_name = DB_NAME() -- Remove this line for all the database
ORDER BY backup_start_date DESC, backup_finish_date
GO
This is ok if you are using a native sql backup - We use Comvault and this still populates the backup_finish_date even if the job fails 🙁
UPDATE - Scrub that - Comvault records the successful re-try but does not log the initial failure in msdb.dbo.backupset
You could also use Exec msdb.dbo.sp_help_jobhistory @run_status = 0 to get a quick list of all failed jobs
SQL DBA
Every day is a school day, and don't trust anyone who tells you any different.
http://sqlblogness.blogspot.co.uk
April 16, 2013 at 11:48 am
SELECT sysjobhistory.server,sysjobs.name AS job_name,
CASE sysjobhistory.run_status
WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Succeeded'
END AS run_status
FROM msdb.dbo.sysjobhistory
INNER JOIN msdb.dbo.sysjobs
ON msdb.dbo.sysjobhistory.job_id = msdb.dbo.sysjobs.job_id
Where sysjobs.name = 'YourBackupJobName'
May 27, 2013 at 3:44 pm
Hi,
Is the script applicable on SQL Server 2000, 2005 ?
Thanks
dev1
May 28, 2013 at 5:58 pm
dev1.bohol (5/27/2013)
Hi,Is the script applicable on SQL Server 2000, 2005 ?
Thanks
dev1
Which script are you referring to?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply