InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 21:33:51 GMT20http://www.sqlservercentral.com/Forums/Topic1381652-392-1.aspx[quote][b]sapen (11/7/2012)[/b][hr]Thanks for the script[/quote] You're Welcome Sapen. :-)Wed, 07 Nov 2012 20:48:48 GMTvinu512http://www.sqlservercentral.com/Forums/Topic1381652-392-1.aspx[quote][b]capnhector (11/7/2012)[/b][hr]if your just looking for a report of backups you can query msdb.dbo.backupset which contains allot of information[code="sql"]select top 10 b.database_name, b.backup_start_date, b.backup_finish_date from msdb.dbo.backupset b[/code]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.[/quote]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.Wed, 07 Nov 2012 14:55:11 GMTSapenhttp://www.sqlservercentral.com/Forums/Topic1381652-392-1.aspxif your just looking for a report of backups you can query msdb.dbo.backupset which contains allot of information[code="sql"]select top 10 b.database_name, b.backup_start_date, b.backup_finish_date from msdb.dbo.backupset b[/code]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.Wed, 07 Nov 2012 13:52:47 GMTCapnHectorhttp://www.sqlservercentral.com/Forums/Topic1381652-392-1.aspxThanks for the scriptWed, 07 Nov 2012 11:52:15 GMTSapenhttp://www.sqlservercentral.com/Forums/Topic1381652-392-1.aspxYou can use the following script to query the Error Log and get result for all Database backups and restores:[code="sql"]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'[/code] You can alter the Script according to your requirement. Hope this is close to what you are looking for.Wed, 07 Nov 2012 04:50:28 GMTvinu512http://www.sqlservercentral.com/Forums/Topic1381652-392-1.aspxHello,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 inputsTue, 06 Nov 2012 09:52:44 GMTSapen