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

TSQL to check database backup status Expand / Collapse
Author
Message
Posted Tuesday, November 6, 2012 9:52 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, August 25, 2014 7:34 AM
Points: 1,322, Visits: 1,730
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
Post #1381652
Posted Wednesday, November 7, 2012 4:50 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 11:52 PM
Points: 1,126, Visits: 1,590
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.


Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden
Post #1381912
Posted Wednesday, November 7, 2012 11:52 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, August 25, 2014 7:34 AM
Points: 1,322, Visits: 1,730
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
Post #1382114
Posted Wednesday, November 7, 2012 1:52 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 12:40 PM
Points: 945, Visits: 1,771
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 faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw

Need to Split some strings? Jeff Moden's DelimitedSplit8K
Jeff Moden's Cross tab and Pivots Part 1
Jeff Moden's Cross tab and Pivots Part 2

Jeremy Oursler
Post #1382167
Posted Wednesday, November 7, 2012 2:55 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, August 25, 2014 7:34 AM
Points: 1,322, Visits: 1,730
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
Post #1382183
Posted Wednesday, November 7, 2012 8:48 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 11:52 PM
Points: 1,126, Visits: 1,590
sapen (11/7/2012)
Thanks for the script


You're Welcome Sapen.


Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden
Post #1382245
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse