Backup Status Script...

Question

Post reply

Backup Status Script...

fawad.majid

Mr or Mrs. 500

Points: 551
More actions
September 18, 2014 at 8:06 am

#290087

Hi people ...
I need your help in this ..
i got a powershell script which gives me the backup Status for all Databases with backup type (Full DB, Trans-log & Diff) and get me the result in HTML format...
the problem is i when i run this script it generates report for all the databases including the system databases and I WANT TO DO RUN THIS ONLY FOR A SPECIFIC Database...
here is the script
$ServerList = Get-Content "C:\ServerList.txt"
$OutputFile = "C:\Jobs\Output.htm"
$HTML = '<style type="text/css">
#Header{font-family:"Trebuchet MS", Arial, Helvetica, sans-serif;width:100%;border-collapse:collapse;}
#Header td, #Header th {font-size:14px;border:1px solid #98bf21;padding:3px 7px 2px 7px;}
#Header th {font-size:14px;text-align:left;padding-top:5px;padding-bottom:4px;background-color:#A7C942;color:#fff;}
#Header tr.alt td {color:#000;background-color:#EAF2D3;}
</Style>'
$HTML += "<HTML><BODY><Table border=1 cellpadding=0 cellspacing=0 width=100% id=Header>
<TR>
<TH><B>Database Name</B></TH>
<TH><B>RecoveryModel</B></TD>
<TH><B>Last Full Backup Date</B></TH>
<TH><B>Last Differential Backup Date</B></TH>
<TH><B>Last Log Backup Date</B></TH>
</TR>"
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
ForEach ($ServerName in $ServerList)
{
$HTML += "<TR bgColor='#ccff66'><TD colspan=5 align=center><B>$ServerName</B></TD></TR>"
$SQLServer = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $ServerName
Foreach($Database in $SQLServer.Databases)
{
$HTML += "<TR>
<TD>$($Database.Name)</TD>
<TD>$($Database.RecoveryModel)</TD>
<TD>$($Database.LastBackupDate)</TD>
<TD>$($Database.LastDifferentialBackupDate)</TD>
<TD>$($Database.LastLogBackupDate)</TD>
</TR>"
}
}
$HTML += "</Table></BODY></HTML>"
$HTML | Out-File $OutputFile
Can someone help me in this ..
Regards

Viewing 6 posts - 1 through 5 (of 5 total)

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

Just add a check for the database name you want:

$ServerList = Get-Content "C:\ServerList.txt"

$OutputFile = "C:\Jobs\Output.htm"

$HTML = '<style type="text/css">

#Header{font-family:"Trebuchet MS", Arial, Helvetica, sans-serif;width:100%;border-collapse:collapse;}

#Header td, #Header th {font-size:14px;border:1px solid #98bf21;padding:3px 7px 2px 7px;}

#Header th {font-size:14px;text-align:left;padding-top:5px;padding-bottom:4px;background-color:#A7C942;color:#fff;}

#Header tr.alt td {color:#000;background-color:#EAF2D3;}

</Style>'

$HTML += "<HTML><BODY><Table border=1 cellpadding=0 cellspacing=0 width=100% id=Header>

<TR>

<TH><B>Database Name</B></TH>

<TH><B>RecoveryModel</B></TD>

<TH><B>Last Full Backup Date</B></TH>

<TH><B>Last Differential Backup Date</B></TH>

<TH><B>Last Log Backup Date</B></TH>

</TR>"

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

ForEach ($ServerName in $ServerList)

{

$HTML += "<TR bgColor='#ccff66'><TD colspan=5 align=center><B>$ServerName</B></TD></TR>"

$SQLServer = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $ServerName

Foreach($Database in $SQLServer.Databases)

{

# add a filter for a specific database

if($Database.Name -eq "YourDatabaseName")

{

$HTML += "<TR>

<TD>$($Database.Name)</TD>

<TD>$($Database.RecoveryModel)</TD>

<TD>$($Database.LastBackupDate)</TD>

<TD>$($Database.LastDifferentialBackupDate)</TD>

<TD>$($Database.LastLogBackupDate)</TD>

</TR>"

}

$HTML += "</Table></BODY></HTML>"

$HTML | Out-File $OutputFile

-- Gianluca Sartori

fawad.majid Mr or Mrs. 500 Points: 551 More actions · Answer 2

Thank you very much Gianluca Sartori...

you are a life saver ...

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

spaghettidba

SSC Guru

Points: 105732

September 18, 2014 at 9:30 am

#1746459

Welcome

-- Gianluca Sartori

fawad.majid Mr or Mrs. 500 Points: 551 More actions · Answer 4

hi Gianluca Sartori

The Script run perfectly and output looks great..

I just encounter one problem when i run the script, it gives this error on 5 of my servers and rest went well

the error is:

The following exception occurred while trying to enumerate the collection: Failed to connect to server (ServerName).".

At line :5 char:22

+foreach($Database in $Sqlserver.Databases)

+ ~~~~~~~~~~~~~~~

+Categoryinfo : NotSpecified: (:)[] extendedtypesystemexception

+FullyQualifiedErrorID: ExceptionInGetEnumerator

this is the error i am getting on 5 servers out of 72 servers

can you please look into this

spaghettidba SSC Guru Points: 105732 More actions · Answer 5

The error is quite self explanatory: Failed to connect to server means it could't connect to the server.

Check if the server is running and spelled correctly in the serverlist file.

Also check that you don't have extra empty lines in the file

-- Gianluca Sartori