Query to return SQL Server Backup Job Details

Posted on 2010-11-09
Last Modified: 2012-05-10

Can you guys recommend a SQL query in MS SQL Server 2008 to return SQL Server backup job details such as...

Server, Client, FileName, Size, Start Time, End Time etc.

Many thanks,

Question by:mullykid
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 34091603

check out this link.

It may helps....
LVL 58

Accepted Solution

cyberkiwi earned 250 total points
ID: 34091614
select b.server_name, '?' as client, m.physical_device_name, b.backup_size,
      backup_start_date, backup_finish_date,
      b.database_name, b.type, b.database_creation_date
from msdb..backupset b
inner join msdb..backupmediafamily m on b.media_set_id = m.media_set_id
LVL 16

Assisted Solution

EvilPostIt earned 250 total points
ID: 34091626
Try this.

select bs.Server_name,bs.machine_name,bmf.physical_device_name,bs.backup_size,bs.backup_start_date,bs.backup_finish_date from msdb..backupset bs
join msdb..backupmediafamily bmf on bmf.media_set_id=bs.media_set_id
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 34091738
Thanks guys. I'll check those out.

Author Comment

ID: 34092159

That's great. In addition, how do I obtain job status (failed, succes), schedules etc.

Do I need to look in the SysjobXXX tables?

LVL 16

Assisted Solution

EvilPostIt earned 250 total points
ID: 34092183
The queries that we have posted are with regards to the in built backup tables. If you query sysjobhistory table yuo will be able to get this information.

Author Comment

ID: 34092297
Ok. Thanks lads

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql 2014,  lock limit 5 46
Why I am having this error in my query? 2 32
query optimization 6 21
Reformat SQL - so SSRS can read the columns 25 30
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

762 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