List of recent database backups

Usually we need to find list of recent database backups. When the backup was taken and where it was saved. Below script will get a 5 recent backups

DECLARE @DBNAME VARCHAR(128)

SET @DBNAME = 'DB_Name'

SELECT TOP 5 
 A.database_name
 ,B.physical_device_name
 ,A.media_set_id
 ,A.backup_size
 ,A.backup_start_date
 ,A.backup_finish_date
 
FROM msdb.dbo.backupset A
 INNER JOIN msdb.dbo.backupmediafamily B
ON 
 A.media_set_id = B.media_set_id
WHERE 
A.Database_Name= @DBNAME

ORDER BY A.backup_finish_date DESC

Audit server roles membership

When you have a huge environment and some senior developers have sysadmin rights on some of you DB servers(Yeah yeah, I know it's bad) it's good to have a trigger to audit server roles membership. Also it's good to receive an email when server roles membership changes. Below is my trigger to audit server roles membership, I use 2 events: ADD_SERVER_ROLE_MEMBER, DROP_SERVER_ROLE_MEMBER.

ALTER TRIGGER MYSERVERRROLEAUDIT
ON ALL SERVER
FOR ADD_SERVER_ROLE_MEMBER, DROP_SERVER_ROLE_MEMBER
AS

SET NOCOUNT ON
DECLARE @eventtable TABLE (EventType NVARCHAR(MAX), ObjectName varchar(100), EventDate datetime, CurrentUser varchar(100), TSQLCommand NVARCHAR(MAX))
DECLARE @EventType NVARCHAR(MAX)
DECLARE @SchemaName NVARCHAR(MAX)
DECLARE @DBName VARCHAR(100)
DECLARE @Subj VARCHAR(200)
DECLARE @ObjectName varchar(100)
DECLARE @ObjectType varchar(100)
DECLARE @Message NVARCHAR(MAX)
DECLARE @TSQL NVARCHAR(MAX)

SELECT 
 @EventType = EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(max)')  
,@SchemaName = EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]','nvarchar(max)')  
,@ObjectName = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(max)')
,@ObjectType = EVENTDATA().value('(/EVENT_INSTANCE/ObjectType)[1]','nvarchar(max)')
,@DBName = EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(max)')
,@TSQL = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')

insert into @eventtable
select @EventType, @ObjectName, getdate(), SUSER_SNAME(), @TSQL


SET @Message =N'' +
    N'' +
    N'' +
    N'' +
    N'' +
    N'' +
    CAST ( ( SELECT td = EventType, '',
    td = ObjectName, '',
    td = EventDate, '',
    td = CurrentUser, '',
    td = TSQLCommand, ''
    FROM  @eventtable
FOR XML PATH('tr'), TYPE 
    ) AS NVARCHAR(MAX) ) +
    N'
Event Type
Object Name
Date
Current User
TSQL Command
' 

SELECT @Subj='Role membership has changed on '+@@servername+' server'   
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'your_profile_name',
    @recipients = 'your@distributionlist.com',
    @body = @Message,
    @importance= 'High',
    @subject =@Subj,
    @body_format = 'HTML';

SET NOCOUNT OFF
GO