Database Backup Report

Leave a comment (3) Go to comments

The need to back up databases on a regular basis is a very major component of managing system. Backups may be used to provide a means of recovery from a disaster situation. But at times, we forget to document backup report, which might be required during restores.

Here is a solution to this, The following Script will give you a detailed report about your backups, which are being performed in last 7 (Seven) days ( This value is adjustable, you can specify your own period)

———————————————————

–Database

Backups for all databases For Previous Week

———————————————————

SELECT


CONVERT(CHAR(100), ‘TEST-Server’) AS Server,

msdb.dbo.backupset.database_name,

msdb.dbo.backupset.backup_start_date,

msdb.dbo.backupset.backup_finish_date,

msdb.dbo.backupset.expiration_date,

CASE msdb..backupset.type

WHEN ‘D’ THEN ‘Database’

WHEN ‘L’ THEN ‘Log’

END AS
backup_type,

msdb.dbo.backupset.backup_size,

msdb.dbo.backupmediafamily.logical_device_name,

msdb.dbo.backupmediafamily.physical_device_name,

msdb.dbo.backupset.name AS
backupset_name,

msdb.dbo.backupset.description

FROM msdb.dbo.backupmediafamily

INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id =
msdb.dbo.backupset.media_set_id

WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date,
102) >=
GETDATE()
- 7)

ORDER BY

msdb.dbo.backupset.database_name,

msdb.dbo.backupset.backup_finish_date



OUTPUT

This Script will give you information which covers

  • ServerName , where Script is Executed
  • Database name, which was backed up
  • Backup Start Time
  • Backup end Time
  • Backup Type
  • Backup Size
  • logical device name
  • Physical Device name
  • backupset name
  • backup description
Database Backup Report sql server backup report sql backup report script to list down your backups Database Backup Report backup report script backup report

SQL database Database Backup Report

In next post we will put a article to know, how to get a list of databases that have not been getting backed up


EOF - Database Backup Report, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000
Leave a comment

3 Comments.

  1. Im trying to build an Enterprise Backup report for SSRS, and do you know of a way to choose a parameter for the instances first, and pass that information to the dataset Query? so it will reach out to each instance and return the information for all instances in a report?

  2. In the article “Database Backup Report” dated 06/14/2010, there is a reference to an upcoming article “List of databases that have not been getting backed up”. I haven’t been able to locate that article on this website. Was it ever written? If so, can you send me the link to it?

    Thanks

  3. DBATAG,

    This is great script, I was tring to figure this out from last 4-5 days. Thanks for ready made material. I am quite sure, being a DBA you all developed this.

    I am seeing you are posting good material from last 4 days. Keep the momentum going.

    Thanks

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.