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
