Database Backup Information Scripts

Question

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

SQL Guy 1 SSCoach Points: 15979 More actions · Answer 1

Good scripts.

Couple of notes:

1. Besides D and L types of backup, there is also P = partial.

2. It would be also helpful to include database_name in select part of "Most Recent Database Backup for Each Database - Detailed"

Lee Linares SSCrazy Points: 2730 More actions · Answer 2

Sathya,

Thanks for sharing the script with us. To add to SQL Guy 1's comment, you should also include I (Differential) to backup types. Thanks again.

Lee

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 3

SSC Guru

Points: 76202

November 25, 2015 at 6:51 am

Nice script, thanks.

SQL Galaxy One Orange Chip Points: 29478 More actions · Answer 4

Hi Satya.. this is excellent script for sharing..

How can I add linked server name in this script for getting backup report in one single format.

I have created linked server, Name is : MNDLIVPROD

SELECT

CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,

msdb.dbo.backupset.database_name,

msdb.dbo.backupset.backup_start_date,

msdb.dbo.backupset.backup_finish_date,

--msdb.dbo.backupset.expiration_date,

CASE msdb..backupset.type

WHEN 'D' THEN 'Database'

WHEN 'L' THEN 'Log'

END AS backup_type,

msdb.dbo.backupset.backup_size,

msdb.dbo.backupmediafamily.logical_device_name,

msdb.dbo.backupmediafamily.physical_device_name

--msdb.dbo.backupset.name AS backupset_name,

--msdb.dbo.backupset.description

FROM msdb.dbo.backupmediafamily

INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id

WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7)

ORDER BY

msdb.dbo.backupset.database_name,

msdb.dbo.backupset.backup_finish_date

Cookies on SQLServerCentral