SQL SERVER – Get Database Backup History for a Single Database

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 databaseSELECT 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_modelFROM msdb.dbo.backupset sINNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_idWHERE s.database_name = DB_NAME() -- Remove this line for all the databaseORDER 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.

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 .

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

I have noticed in my one database above scripts shows the only 3 latest entries of backup history. Any idea why it is showing only tree entries but actually job is taking the backup from from last two weeks.

I want the script which i can find latest full and tlog backup details because i have so many server and provide me the steps also to get data in excel sheet table required in queries ‘server name ,hostname,bacuptype,
databasename,backuppath,backuptime,

Community Initiatives

About Pinal Dave

Pinal Dave is a Pluralsight Developer Evangelist. He has authored 11 SQL Server database books, 17 Pluralsight courses and have written over 3200 articles on the database technology on his blog at a http://blog.sqlauthority.com. Along with 11+ years of hands on experience he holds a Masters of Science degree and a number of certifications, including MCTS, MCDBA and MCAD (.NET). His past work experiences include Technology Evangelist at Microsoft and Sr. Consultant at SolidQ. Follow @pinaldave
Send Author Pinal Dave
an email at pinal@sqlauthority.com

Email Subscription

Enter your email address to subscribe to this blog and receive notifications of new posts by email.