Question :
I need a power-shell script to obtain the following information Name of the database , LastBackupDate of the database and this information should be sent via an email converting the results to HTML format 🙂 if we do have chance of getting the script done which will be .ps1 file I need to know how to run as a sql job.
Answer :
Code gets backup history for all databases on a server and generates HTML report
Step 1:
DECLARE @Body VARCHAR(MAX),
@TableHead VARCHAR(MAX),
@TableTail VARCHAR(MAX)
SET NoCount ON ;
SET @TableTail = '</body></html>' ;
SET @TableHead = '<html><head>' + '<style>'
+ 'td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:10pt;} '
+ '</style>' + '</head>' + '<body>'
SELECT @Body = ''
SELECT @Body = @Body + '<table cellpadding=0 cellspacing=0 border=0>'
+ '<tr><td bgcolor=#E6E6FA>Database Name </td>'
+ '<td width="100px" bgcolor=#E6E6FA>Backup Size</td>'
+ '<td width="100px" bgcolor=#E6E6FA>Time Taken</td>'
+ '<td width="100px" bgcolor=#E6E6FA>Backup Start Date</td>'
+ '<td width="100px" bgcolor=#E6E6FA><b>First LSN</b></td>'
+ '<td width="100px" bgcolor=#E6E6FA><b>Last LSN</b></td>'
+ '<td width="100px" bgcolor=#E6E6FA><b>Backup Type</b></td>'
+ '<td width="100px" bgcolor=#E6E6FA><b>Server Name</b></td>'
+ '<td width="100px" bgcolor=#E6E6FA><b>Recovery Model</b></td>'
+ '<td width="300px" bgcolor=#E6E6FA>Physical Device Name</td> </tr>'
-- Backup History script of AAsim Adbullah
-- http://blog.sqlauthority.com/2010/11/10/sql-server-get-database-backup-history-for-a-single-database/
SELECT @Body = @Body
+ ( SELECT td = s.database_name, '',
td = CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14))+ ' ' + 'MB', '',
td = CAST(DATEDIFF(second, s.backup_start_date,
s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds', '',
td = s.backup_start_date, '',
td = CAST(s.first_lsn AS VARCHAR(50)), '',
td = CAST(s.last_lsn AS VARCHAR(50)), '',
td = CASE s.[type]
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END,'',
td = s.server_name, '',
td = s.recovery_model,'',
td = m.physical_device_name,''
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE s.backup_start_date >= DATEADD(dd, 0, DATEDIFF(dd, 0,GETDATE() - 1)) -- --- Date check for one day backup history information
ORDER BY backup_start_date DESC,
backup_finish_date
FOR
XML PATH('tr')
) + '</table>'
SET @Body = REPLACE(@Body, ' ', SPACE(1))
SET @Body = REPLACE(@Body, '=', '=')
SET @Body = REPLACE(@Body, '<tr><TRRow>1</TRRow>', '<tr bgcolor=#C6CFFF>')
SET @Body = REPLACE(@Body, '<TRRow>0</TRRow>', '')
SELECT @Body = @TableHead + @Body + @TableTail
IF ( @Body IS NULL )
BEGIN
EXEC msdb.dbo.sp_send_dbmail @recipients='sqlcache@blogspot.com', -- Add Valid Email Id
@subject = 'Backup History Information',
@profile_name = 'AA.SC', -- Change Profile Name
@body = 'No Backup History Found for past 1 Day',
@body_format = 'HTML' ;
END
ELSE
Begin
EXEC msdb.dbo.sp_send_dbmail @recipients='sqlcache@blogspot.com', -- Add Valid Email Id
@subject = 'Backup History Information',
@profile_name = 'AA.SC', -- Change Profile Name
@body = @Body,
@body_format = 'HTML' ;
END
Step 2:
create a new job add this code in step and schedule it according to your requirement
