I recently wrote article SQL SERVER – Finding Last Backup Time for All Database and requested blog readers to respond with their own script which they use it Database Backup.
Here is the script suggested by SQL Expert aasim abdullah, who has written excellent script which goes back and retrieves the history of any single database.
USE AdventureWorks
GO
-- Get Backup History for required database
SELECT TOP 100
s.database_name,
m.physical_device_name,
CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize,
CAST(DATEDIFF(second, s.backup_start_date,
s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken,
s.backup_start_date,
CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn,
CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn,
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
Very neat script and in my above example I have ran that for single database adventureworks and you can see following results. The same can be ran for multiple database as well if you just remove the WHERE condition.
Reference: Pinal Dave (http://blog.SQLAuthority.com)
What does the SELECT TOP 100 mean?
Thanks
Pingback: SQL SERVER – Change Database Access to Single User Mode Using SSMS Journey to SQL Authority with Pinal Dave
Helo, I want to make sql server 2008 Replication with Mirroring. so I have 4 servers i want two of them to be mirrored . and the other two i want the Principle to be replicated to them. So that after fail over i want the mirrored to take the replication. Please help me i’m new to sql server .
Nice Work !!!
Good one.
But what if we need to get the backup history with backupfile size in Kbs?
select A.database_name, A.backup_start_date, A.backup_finish_date,
datediff(ss, A.backup_start_date, A.backup_finish_date) as ‘Duration’,
(case A.[type] when ‘D’ then ‘Full’ when ‘I’ then ‘Differential’ when ‘L’ then ‘Log’
when ‘F’ then ‘File or Filegroup’ when ‘G’ then ‘File Differential’
when ‘P’ then ‘Partial’ when ‘Q’ then ‘Partial Differential’ else A.[type] end) as ‘Type’,
A.backup_size, B.physical_device_name
from msdb.dbo.backupset A
left join msdb.dbo.backupmediafamily B on B.media_set_id = A.media_set_id
order by A.backup_finish_date desc
This will give in KB
How can I get the result of the above script suggested by aasim abdullah on mail
I have configured DB mail and just get successful backup mail of DB.
Pingback: SQL SERVER – Beginning New Weekly Series – Memory Lane – #001 « SQL Server Journey with SQL Authority
Pingback: SQL SERVER – Weekly Series – Memory Lane – #007 « SQL Server Journey with SQL Authority
How to get both success and fail status for daily backup jobs to generate daily report, as i know failed database backup has no entry in msdb.dbo.backupset ?
this is great! thanks! is there a way to see who backed it up?
hey i want to create one history table for hole database .
without creating any link with any table.means whenever i will fire update,delete,insert query that history table automatically insert.
How do we get the “compressed” database backup file size, not the regular backup file size?
@Leon, the backup_size column will give you the compressed size. You need to join to backupmediaset to determine if the backup is compressed.
Sorry, made a mistake. Look at [msdb].[dbo].[backupset].[compressed_backup_size]