November 6, 2012 at 9:52 am
Hello,
I am trying to generate a monthly report for sql database backup job. Is there a script that I can use to query msdb to check the backup success or failure status of a specific database?
Thanks for your inputs
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
November 7, 2012 at 4:50 am
You can use the following script to query the Error Log and get result for all Database backups and restores:
Declare @ArchiveNum Int
Declare @Arch_Temp Table(ArchiveNo Int, Date DateTime, LogFileSize BigInt)
Declare @Err_temp Table(LogDate DateTime, ProcessInfo NVarchar(30), Text Nvarchar(MAX))
Insert Into @Arch_Temp
Execute xp_enumerrorlogs
Declare Arch_Cursor Cursor LOCAL STATIC FORWARD_ONLY
For
Select ArchiveNo From @Arch_Temp
Open Arch_Cursor
Fetch NEXT From Arch_Cursor Into @ArchiveNum
While(@@FETCH_STATUS = 0)
Begin
Insert Into @Err_temp
EXEC sys.xp_readerrorlog @ArchiveNum
Fetch NEXT From Arch_Cursor Into @ArchiveNum
End
Close Arch_Cursor
DeAllocate Arch_Cursor
Select * From @Err_temp Where ProcessInfo = 'Backup'
You can alter the Script according to your requirement. Hope this is close to what you are looking for.
November 7, 2012 at 11:52 am
Thanks for the script
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
November 7, 2012 at 1:52 pm
if your just looking for a report of backups you can query msdb.dbo.backupset which contains allot of information
select top 10 b.database_name, b.backup_start_date, b.backup_finish_date
from msdb.dbo.backupset b
with a join to sys.databases you can eliminate system databases if you dont care about them or can get more detailed info about the databases. no need to run through the error log with a cursor.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
November 7, 2012 at 2:55 pm
capnhector (11/7/2012)
if your just looking for a report of backups you can query msdb.dbo.backupset which contains allot of information
select top 10 b.database_name, b.backup_start_date, b.backup_finish_date
from msdb.dbo.backupset b
with a join to sys.databases you can eliminate system databases if you dont care about them or can get more detailed info about the databases. no need to run through the error log with a cursor.
I was able to pull that information already. But wanted to identify the status of a database backup (failed/succeeded) which doesnt get logged in msdb.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
November 7, 2012 at 8:48 pm
May 7, 2018 at 10:46 am
SELECT r.session_id AS [Session_Id]
,r.command AS [command]
,CONVERT(NUMERIC(6, 2), r.percent_complete) AS [% Complete]
,GETDATE() AS [Current Time]
,CONVERT(VARCHAR(20), DATEADD(ms, r.estimated_completion_time, GetDate()), 20) AS [Estimated Completion Time]
,CONVERT(NUMERIC(32, 2), r.total_elapsed_time / 1000.0 / 60.0) AS [Elapsed Min]
,CONVERT(NUMERIC(32, 2), r.estimated_completion_time / 1000.0 / 60.0) AS [Estimated Min]
,CONVERT(NUMERIC(32, 2), r.estimated_completion_time / 1000.0 / 60.0 / 60.0) AS [Estimated Hours]
,CONVERT(VARCHAR(1000), (
SELECT SUBSTRING(TEXT, r.statement_start_offset / 2, CASE
WHEN r.statement_end_offset = - 1
THEN 1000
ELSE (r.statement_end_offset - r.statement_start_offset) / 2
END) 'Statement text'
FROM sys.dm_exec_sql_text(sql_handle)
))
FROM sys.dm_exec_requests r
WHERE command like 'RESTORE%'
or command like 'BACKUP%'
May 7, 2018 at 11:44 am
You can directly search for "Backup" "failed" messages without having to pull the entire log file first, viz:
EXEC sys.xp_readerrorlog @ArchiveID, 1, N'Backup', N'failed'
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply