‫گزارشگیری از تاریخچه‌ی پشتیبان‌گیری‌ها در اس کیوال سرور

USE master;
SELECT B.name AS Database_Name,
   ISNULL(STR(ABS(DATEDIFF(day, GetDate(),
   MAX(Backup_finish_date)))), 'NEVER') AS DaysSinceLastBackup,
   ISNULL(CONVERT(char(10), MAX(backup_finish_date), 101), 'NEVER') AS
   LastBackupDate
FROM   master.dbo.sysdatabases B
LEFT OUTER JOIN   msdb.dbo.backupset A
ON   A.database_name = B.name
AND   A.type = 'D'
GROUP BY
   B.Name
ORDER BY
   B.name

SELECT bs.server_name,
   bs.database_name AS 'Database Name',
   CONVERT (BIGINT, bs.backup_size / 1048576 ) AS
   'Uncompressed Backup Size (MB)',
   CONVERT (BIGINT, bs.compressed_backup_size / 1048576 ) AS
   'Compressed Backup Size (MB)',
   CONVERT (NUMERIC (20,2), (CONVERT (FLOAT, bs.backup_size) /
   CONVERT (FLOAT, bs.compressed_backup_size))) AS 'Compression Ratio',
   DATEDIFF (SECOND, bs.backup_start_date, bs.backup_finish_date) AS
   'Backup Elapsed Time (sec)',
   bs.backup_finish_date AS 'Backup Finish Date'
FROM   msdb.dbo.backupset AS bs
WHERE  DATEDIFF (SECOND, bs.backup_start_date, bs.backup_finish_date) > 0
AND  bs.backup_size > 0
AND  bs.type = 'D' -- Change to L if you want Log backups
ORDER BY
   bs.backup_finish_date DESC

USE master;
EXEC sp_configure 'backup compression default', '1';
RECONFIGURE WITH OVERRIDE;