I want get html report from log shipping

Question

Post reply

I want get html report from log shipping

manu.manohar033

Old Hand

Points: 328
More actions
December 21, 2017 at 8:29 am

#325151

I want to get HTML report from log shipping, here is my script... please modify it
##setup data source
$dataSource1 = "MANU\PRACTICE"     ##SQL instance name
$database1 = "MASTER"          ##Database name
$dataSource2 = "MANU"     ##SQL instance name
$database2 = "MASTER"          ##Database name
$sqlCommand = "exec sp_help_log_shipping_monitor"     ##The T-SQL command to execute
$TableHeader = "Log_shipping_status"     ##The title of the HTML page
$Todaydate = (Get-date).toString("yyyyMMddhhmmss")
$FileDir="D:\logshipping\" #Directory where you need to save Reports
$OutputFile = $FileDir+$Todaydate+"-LogshippingReport.html"     ##The file location
##set HTML formatting
$Header = @"
<style>
TABLE {border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}
TR:Nth-Child(Even) {Background-Color: #dddddd;}
TH {border-width: 1px;padding: 3px;border-style: solid;border-color: black;background-color: #6495ED;}
TD {border-width: 1px;padding: 3px;border-style: solid;border-color: black;}
</style>
<title>
Log_shipping_status
</title>
"@
$body = @"
"@
$pre="<h1>Log_shipping_status</h1>"
##Create a string variable with all our connection details
$connectionDetails = "Provider=sqloledb; " +
    "Data Source=$dataSource; " +
    "Initial Catalog=$database; " +
    "Integrated Security=SSPI;"
##Connect to the data source using the connection details and T-SQL command we provided above, and open the connection
$connection = New-Object System.Data.OleDb.OleDbConnection $connectionDetails
$command = New-Object System.Data.OleDb.OleDbCommand $sqlCommand,$connection
$connection.Open()
##Get the results of our command into a DataSet object, and close the connection
$dataAdapter = New-Object System.Data.OleDb.OleDbDataAdapter $command
$dataSet = New-Object System.Data.DataSet
$dataAdapter.Fill($dataSet)
$connection.Close()
##Return all of the rows and pipe it into the ConvertTo-HTML cmdlet, and then pipe that into our output file
$dataSet.Tables | Select-Object -Expand Rows |
ConvertTo-HTML -Property server, database_name,last_backup_file, last_copied_file, last_restored_file -head $Header -body $body -PreContent $pre|
Out-File $OutputFile

Viewing 8 posts - 1 through 7 (of 7 total)

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

manu.manohar033 Old Hand Points: 328 More actions · Answer 1

This is my output, Please help me. I want to get copy and restore also

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 2

Steve Jones - SSC Editor

SSC Guru

Points: 734418

December 26, 2017 at 9:37 am

#1973480

What doesn't work?

AlexSQLForums SSChampion Points: 14277 More actions · Answer 3

You suppose to execute this script in the destination server to get the data for those columns.

Alex S

RandomStream Hall of Fame Points: 3735 More actions · Answer 4

What scripting language is this?
Why don't you create a SQL Server Agent job and have it email you in html format directly?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

RandomStream - Thursday, January 4, 2018 4:50 PM
What scripting language is this?
Why don't you create a SQL Server Agent job and have it email you in html format directly?

Looks like PowerShell.
However, I may be wrong.

Sue_H SSC Guru Points: 90860 More actions · Answer 6

Lynn Pettis - Thursday, January 4, 2018 5:47 PM
RandomStream - Thursday, January 4, 2018 4:50 PM
What scripting language is this?
Why don't you create a SQL Server Agent job and have it email you in html format directly?
Looks like PowerShell.
However, I may be wrong.

Mastering it already...yes it's Powershell. Sir 🙂

Sue

Sue_H SSC Guru Points: 90860 More actions · Answer 7

manu.manohar033 - Thursday, December 21, 2017 8:29 AM
I want to get HTML report from log shipping, here is my script... please modify it
##setup data source
$dataSource1 = "MANU\PRACTICE"     ##SQL instance name
$database1 = "MASTER"          ##Database name
$dataSource2 = "MANU"     ##SQL instance name
$database2 = "MASTER"          ##Database name
$sqlCommand = "exec sp_help_log_shipping_monitor"     ##The T-SQL command to execute
$TableHeader = "Log_shipping_status"     ##The title of the HTML page
$Todaydate = (Get-date).toString("yyyyMMddhhmmss")
$FileDir="D:\logshipping\" #Directory where you need to save Reports
$OutputFile = $FileDir+$Todaydate+"-LogshippingReport.html"     ##The file location
##set HTML formatting
$Header = @"
<style>
TABLE {border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}
TR:Nth-Child(Even) {Background-Color: #dddddd;}
TH {border-width: 1px;padding: 3px;border-style: solid;border-color: black;background-color: #6495ED;}
TD {border-width: 1px;padding: 3px;border-style: solid;border-color: black;}
</style>
<title>
Log_shipping_status
</title>
"@
$body = @"
"@
$pre="<h1>Log_shipping_status</h1>"
##Create a string variable with all our connection details
$connectionDetails = "Provider=sqloledb; " +
    "Data Source=$dataSource; " +
    "Initial Catalog=$database; " +
    "Integrated Security=SSPI;"
##Connect to the data source using the connection details and T-SQL command we provided above, and open the connection
$connection = New-Object System.Data.OleDb.OleDbConnection $connectionDetails
$command = New-Object System.Data.OleDb.OleDbCommand $sqlCommand,$connection
$connection.Open()
##Get the results of our command into a DataSet object, and close the connection
$dataAdapter = New-Object System.Data.OleDb.OleDbDataAdapter $command
$dataSet = New-Object System.Data.DataSet
$dataAdapter.Fill($dataSet)
$connection.Close()
##Return all of the rows and pipe it into the ConvertTo-HTML cmdlet, and then pipe that into our output file
$dataSet.Tables | Select-Object -Expand Rows |
ConvertTo-HTML -Property server, database_name,last_backup_file, last_copied_file, last_restored_file -head $Header -body $body -PreContent $pre|
Out-File $OutputFile

The script itself runs fine - just ran it.
The issue looks like something with log shipping itself. Manually execute sp_help_log_shipping_monitor in the master database on the primary - if there are no rows for the copy or the restore then it's that process itself.
Check the LS_copy job and the LS_restore job on the secondary and make sure SQL Server Agent is running on the secondary.

Sue