SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


TSQL to check database backup status


TSQL to check database backup status

Author
Message
sasken
sasken
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6156 Visits: 2011
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
vinu512
vinu512
SSCertifiable
SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)

Group: General Forum Members
Points: 6403 Visits: 1626
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 ;-)
sasken
sasken
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6156 Visits: 2011
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
CapnHector
CapnHector
SSCertifiable
SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)

Group: General Forum Members
Points: 6071 Visits: 1789
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
sasken
sasken
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6156 Visits: 2011
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
vinu512
vinu512
SSCertifiable
SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)

Group: General Forum Members
Points: 6403 Visits: 1626
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 ;-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search