Asd

Thursday, June 23, 2011

Different SQL Script for finding Last Backup History for All Database.

Below script is very helpful when you need to find last backups, physical device, and backup size for a database.

The msdb system database is the primary repository for storage of SQL Agent, backup, Service Broker, Database Mail, Log Shipping, restore, and maintenance plan metadata.

We can get all the information for this task out of below msdb System Table: 

      msdb.dbo.backupset.  - Provides information concerning the most-granular details of the backup process .The important columns of note for this object are itemized below.  Asterisks denote the columns we'll be using in this query:
§  database_name - name of the database the backup file pertains to.
§  type - Type of backup process performed:
·         D = Database
·         F = File or Filegroup
·         G = Differential File
·         I = Differential (database)
·         L = Transaction Log
§  backup_finsh_date - time when the backup process completed.
§  backup_start_date - time the backup process was initiated.
§  name - name of the backupset
§  user_name - user performing the backup process
§  expiration_date - date the backup expires

 
SQL Scripts 1

SELECT
      sys.sysdatabases.Name AS DATABASE_NAME,
      COALESCE(CONVERT(VARCHAR(12),MAX(msdb.dbo.backupset.backup_finish_date), 101),'NEVER') AS LAST_DATABASE_BACKUP_DATE
FROM
      sys.sysdatabases
      LEFT OUTER JOIN msdb.dbo.backupset
      ON msdb.dbo.backupset.database_name = sys.sysdatabases.name
GROUP BY
      sys.sysdatabases.Name 

SQL Scripts 2

SELECT
      msdb.dbo.backupmediafamily.physical_device_name AS FILE_LOCATION,
      msdb.dbo.backupset.database_name AS DATABASE_NAME,
      CAST(CAST(msdb.dbo.backupset.backup_size / 1000000 AS INT) AS VARCHAR(14))+ ' ' + 'MB'  AS BACKUP_SIZE,
      CAST(DATEDIFF(second, msdb.dbo.backupset.backup_start_date,      msdb.dbo.backupset.backup_finish_date) AS VARCHAR(4)) + ' '+ 'Seconds' AS BACKUP_TIME_TAKEN,
      msdb.dbo.backupset.backup_start_date AS BACKUP_DATE_TIME,
      CASE msdb.dbo.backupset.[type]
      WHEN 'D' THEN 'Full'
      WHEN 'I' THEN 'Differential'
      WHEN 'L' THEN 'Transaction Log'
      END AS BACKUP_TYPE,
      msdb.dbo.backupset.server_name AS SERVER_NAME
FROM
      msdb.dbo.backupset
      INNER JOIN msdb.dbo.backupmediafamily 
      ON msdb.dbo.backupset.media_set_id = msdb.dbo.backupmediafamily.media_set_id

SQL Scripts 3

SELECT
      ISNULL(STR(ABS(DATEDIFF(day, GetDate(),MAX(Backup_finish_date)))), 'NEVER') AS DAY_LAST_BACKUP_TAKEN,
      master.dbo.sysdatabases.name AS DATABASE_NAME,
      ISNULL(Convert(char(10), MAX(backup_finish_date), 101), 'NEVER') AS LAST_BACKUP_DATE
FROM
      master.dbo.sysdatabases
      LEFT OUTER JOIN msdb.dbo.backupset
      ON msdb.dbo.backupset.database_name = master.dbo.sysdatabases.name
      AND msdb.dbo.backupset.type = 'D'
GROUP BY
      master.dbo.sysdatabases.Name

SQL Scripts 4

SELECT
      database_id AS DATABASE_ID,
      convert(varchar(25), sys.databases.name) AS DATABASE_NAME,
      convert(varchar(10), Databasepropertyex(name, 'status')) AS DATABASE_STATUS,
      (SELECT COUNT(1) FROM sys.master_files WHERE DB_NAME(database_id) = sys.databases.name AND type_desc = 'rows') AS DATAFILES ,
      (SELECT SUM((size*8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = sys.databases.name AND type_desc = 'rows') AS DATA_FILE_SIZE_IN_MB,
      (SELECT COUNT(1) FROM sys.master_files WHERE DB_NAME(database_id) = sys.databases.name AND type_desc = 'log') AS LOG_FILES,
      (SELECT SUM((size*8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = sys.databases.name AND type_desc = 'log') AS LOG_FILE_SIZE_IN_MB,
      CASE compatibility_level
      WHEN 60 THEN '60 (SQL Server 6.0)'
      WHEN 65 THEN '65 (SQL Server 6.5)'
      WHEN 70 THEN '70 (SQL Server 7.0)'
      WHEN 80 THEN '80 (SQL Server 2000)'
      WHEN 90 THEN '90 (SQL Server 2005)'
      END AS COMPATIBILITY_LEVEL,
      CONVERT(VARCHAR(20), create_date, 103) + ' ' + CONVERT(VARCHAR(20), create_date, 108) AS DATABASE_CREATION_DATE,
      CASE WHEN is_fulltext_enabled = 1 THEN 'Fulltext enabled' ELSE ' ' END AS FULL_TEXT,
      CASE WHEN is_auto_close_on = 1 THEN 'autoclose' ELSE ' ' END AS AUTOCLOSE,
      CASE WHEN is_read_only = 1 THEN 'read only' ELSE ' ' END AS  READ_ONLY,
      CASE WHEN is_auto_shrink_on = 1 THEN 'autoshrink' ELSE ' ' END AS AUTOSHRINK,
      CASE WHEN is_auto_create_stats_on = 1 THEN 'auto create statistics' ELSE ' ' END AS AUTO_CREATE_STATISTICS,
      CASE WHEN is_auto_update_stats_on = 1 THEN 'auto update statistics' ELSE ' ' END AS AUTO_UPDATE_STATISTICS,
      CASE WHEN is_in_standby = 1 THEN 'standby' ELSE ' ' END AS STAND_BY,
      CASE WHEN is_cleanly_shutdown = 1 THEN 'cleanly shutdown' ELSE ' ' END AS CLEANLY_SHOUTDOWN,
      user_access_desc AS USER_ACCESS,
      recovery_model_desc AS  RECOVERY_MODEL
FROM
      sys.databases


Posted by - SUHAS R. KUDEKAR (MCTS - Microsoft Business Intelligence)
Learning Office 2010 + SharePoint 2010 + SQL Server 2008 R2