How Do I Use PowerShell to Get a SQL Server Backup Status? 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.
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 = '&lt;/body&gt;&lt;/html&gt;' ;

    SET @TableHead = '&lt;html&gt;&lt;head&gt;' + '&lt;style&gt;'
        + 'td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:10pt;} '
        + '&lt;/style&gt;' + '&lt;/head&gt;' + '&lt;body&gt;'


    SELECT  @Body = ''



    SELECT  @Body = @Body + '&lt;table cellpadding=0 cellspacing=0 border=0&gt;'
            + '&lt;tr&gt;&lt;td bgcolor=#E6E6FA&gt;Database Name &lt;/td&gt;'
            + '&lt;td width="100px" bgcolor=#E6E6FA&gt;Backup Size&lt;/td&gt;'
            + '&lt;td width="100px" bgcolor=#E6E6FA&gt;Time Taken&lt;/td&gt;'
            + '&lt;td width="100px" bgcolor=#E6E6FA&gt;Backup Start Date&lt;/td&gt;'
            + '&lt;td width="100px" bgcolor=#E6E6FA&gt;&lt;b&gt;First LSN&lt;/b&gt;&lt;/td&gt;'
            + '&lt;td width="100px" bgcolor=#E6E6FA&gt;&lt;b&gt;Last LSN&lt;/b&gt;&lt;/td&gt;'
            + '&lt;td width="100px" bgcolor=#E6E6FA&gt;&lt;b&gt;Backup Type&lt;/b&gt;&lt;/td&gt;'
            + '&lt;td width="100px" bgcolor=#E6E6FA&gt;&lt;b&gt;Server Name&lt;/b&gt;&lt;/td&gt;'
            + '&lt;td width="100px" bgcolor=#E6E6FA&gt;&lt;b&gt;Recovery Model&lt;/b&gt;&lt;/td&gt;'
            + '&lt;td width="300px" bgcolor=#E6E6FA&gt;Physical Device Name&lt;/td&gt;   &lt;/tr&gt;'


    -- 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 &gt;= 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')
              ) + '&lt;/table&gt;'



    SET @Body = REPLACE(@Body, '_x0020_', SPACE(1))
    SET @Body = REPLACE(@Body, '_x003D_', '=')
    SET @Body = REPLACE(@Body, '&lt;tr&gt;&lt;TRRow&gt;1&lt;/TRRow&gt;', '&lt;tr bgcolor=#C6CFFF&gt;')
    SET @Body = REPLACE(@Body, '&lt;TRRow&gt;0&lt;/TRRow&gt;', '')


    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