This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- Source: http://dba.stackexchange.com/questions/11005/sql-server-2008-r2-query-last-successful-database-backup-for-all-dbs | |
SELECT TOP 100 | |
s.database_name, | |
m.physical_device_name, | |
CAST(DATEDIFF(second, s.backup_start_date, | |
s.backup_finish_date) AS VARCHAR(100)) + ' ' + 'Seconds' TimeTaken, | |
s.backup_start_date, | |
CASE s.[type] | |
WHEN 'D' THEN 'Full' | |
WHEN 'I' THEN 'Differential' | |
WHEN 'L' THEN 'Transaction Log' | |
END AS BackupType, | |
s.server_name, | |
s.recovery_model | |
FROM msdb.dbo.backupset s | |
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id | |
WHERE s.database_name = DB_NAME() -- Remove this line for all the database | |
ORDER BY backup_start_date DESC, backup_finish_date | |
GO |
Result:

Source:
http://dba.stackexchange.com/questions/11005/sql-server-2008-r2-query-last-successful-database-backup-for-all-dbs
沒有留言:
張貼留言