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.
-
2@@Pathum Anjana why back history information using Power-shell and not from MSDB tables ? as you want to schedule a job for html output?– AA.SCOct 30, 2014 at 5:58
-
As long as I can get the following information and serve my purpose I'm fine with it :) let me know your answer– HeisenbergOct 30, 2014 at 6:11
Add a comment
|
1 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, '_x0020_', SPACE(1))
SET @Body = REPLACE(@Body, '_x003D_', '=')
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='[email protected]', -- 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='[email protected]', -- 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
-
Wow, you've hard-coded your table to be over 5,000px wide. How many monitors does that span? Oct 30, 2014 at 13:55
-
Thanks @AaronBertrand you mentioned my mistake, I updated script. but width="100px" is optional property of TD tag.– AA.SCOct 30, 2014 at 14:02