[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Get Backup History informations

Posted on 2008-02-08
4
Medium Priority
?
1,102 Views
Last Modified: 2012-06-27
Hi all,

Need T-SQL code that extracts the following information from the MSSQL 2000 & 2005 backup history tables within the msdb databases:
1. Backup job name: example FULL_BKUP or Shrink databases
2. Start time
3. End time
4. Results of the backup job (failed, sucess)

its urgent, can anyone help me
0
Comment
Question by:mahboob
4 Comments
 
LVL 53

Expert Comment

by:Vitor Montalvão
ID: 20848785
You have a lot of information about backups in BackupSet table in MSDB database. Just check it and see what information you want to extract from there:

USE msdb
GO

SELECT * FROM backupset
0
 
LVL 18

Expert Comment

by:brejk
ID: 20848817
You seem to be looking for jobs history rather than backup history, so...

SELECT j.[name], j.description, h.run_date, h.run_time, h.run_duration, h.run_status
FROM msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobhistory h
ON j.job_id = h.job_id

Legend: run_date is the date of start, run_time is a time of start, run_duration is duration in HHMMSS format, run_status returns 0 if the job failed and 1 if it succeeded.
0
 

Author Comment

by:mahboob
ID: 20848867
i want t-sql command, with them we display the informations
0
 
LVL 17

Accepted Solution

by:
dbaSQL earned 250 total points
ID: 20850038
try something like this:

create procedure usp_BackupDetails
as

SET NOCOUNT ON
SELECT Database_Name,
    CONVERT( SmallDateTime , MAX(Backup_Finish_Date)) as Last_Backup,
    DATEDIFF(d, MAX(Backup_Finish_Date), Getdate()) as Days_Since_Last
FROM MSDB.dbo.BackupSet
WHERE Type = 'd'
GROUP BY Database_Name
ORDER BY 3 DESC

-- Find the backup of Transaction Log files
SELECT Database_Name,
    CONVERT( SmallDateTime , MAX(Backup_Finish_Date)) as Last_Backup,
    DATEDIFF(d, MAX(Backup_Finish_Date), Getdate()) as Days_Since_Last
FROM MSDB.dbo.BackupSet
WHERE Type = 'l'
GROUP BY Database_Name
ORDER BY 3 DESC



0

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question