Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Query to return SQL Server Backup Job Details

Posted on 2010-11-09
7
Medium Priority
?
394 Views
Last Modified: 2012-05-10
Hi,

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,

Iain
0
Comment
Question by:mullykid
7 Comments
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 34091603
Hi,

check out this link.

http://www.yaldex.com/sql_server_tutorial_3/ch08lev1sec6.html

It may helps....
0
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 1000 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
0
 
LVL 16

Assisted Solution

by:EvilPostIt
EvilPostIt earned 1000 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
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 

Author Comment

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

Author Comment

by:mullykid
ID: 34092159
Guys,

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

Do I need to look in the SysjobXXX tables?

Iain
0
 
LVL 16

Assisted Solution

by:EvilPostIt
EvilPostIt earned 1000 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.
0
 

Author Comment

by:mullykid
ID: 34092297
Ok. Thanks lads
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

971 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