Have you recently taken your SQL Server backups ?

Available SQL Server backups

Have you recently taken your SQL Server backups ? Where I work we do full database backup on disk with T-SQL BACKUP command. Those backup files are then put on tape using Tivoli Storage Manager (TSM).

The below T-SQL will list backups that have been taken, or not (backup_start_date column will be NULL), for all your databases (outer join) within the last 10 days:

SELECT
  e.name AS database_name,
  d.backup_start_date,
  d.backup_finish_date,
  DATEDIFF(mi,d.backup_start_date,d.backup_finish_date) AS "backup duration (mi)",
  d.name,
  d.physical_device_name,
  CAST(ROUND(d.backup_size/(1024*1024),0) AS INT) AS "backup size (MB)",
  d.is_compressed,
  d.recovery_model
FROM
  (
    SELECT
      a.backup_set_id,
      a.name,
      a.backup_start_date,
      a.backup_finish_date,
      a.backup_size,
      a.database_name,
      a.recovery_model,
      b.is_compressed,
      c.physical_device_name
    FROM
      msdb.dbo.backupset a,
      msdb.dbo.backupmediaset b,
      msdb.dbo.backupmediafamily c
    WHERE a.media_set_id = b.media_set_id
    AND b.media_set_id = c.media_set_id
    AND a.type = 'D'
    AND a.backup_start_date > GETDATE()-10
  )
  d
RIGHT OUTER JOIN sys.databases e
ON d.database_name = e.name
ORDER BY d.backup_set_id DESC;

Where type can take below value:

Backup type. Can be:
D = Database
I = Differential database
L = Log
F = File or filegroup
G =Differential file
P = Partial
Q = Differential partial
Can be NULL.

References

About Post Author

Share the knowledge!

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=""> <s> <strike> <strong>