Thursday, November 17, 2011

0 Find out Backup & Restore History from MSDB

We can find out Backup & Restore History from below 2 tables in MSDB.

  • backupset: Backup History
  • restorehistory: Restore History.

Below Query list all the database backup history

  1. SELECT a.name, b.type, b.backup_finish_date AS LastSuccessfulBackup,b.backup_finish_date
  2. FROM master..sysdatabases a
  3. LEFT OUTER JOIN msdb..backupset b ON a.name = b.database_name
  4. ORDER BY a.name, b.type

D means the database backup. L is the transaction log bakcupimage

Restore history is much more straight forward

  1. select * from restorehistory

image

Reference

Viewing Information About Backups http://msdn.microsoft.com/en-us/library/ms188653.aspx

Backup/Restore history  http://www.sqlbackuprestore.com/backuprestorehistorytables.htm

0 comments:

Post a Comment

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates