# encoding: ascii
# api: powershell
# title: SQL Log Backup
# description: This is the log backup script to be used with the Async Backup Script http://poshcode.org/2787
# version: 0.1
# type: script
# author: Justin
# license: CC0
# x-poshcode-id: 2788
# x-archived: 2011-07-16T11:25:29
# x-published: 2011-07-13T12:15:00
#
# The log backup is NOT async
# enjoy!
# Justin
# jrich523.wordpress.com
#
## log backup
$backuppath = "\\server\sqlbackups\"
$alertaddress = "jrich@domain.com"
$smtp = "smtp.domain.com"
$hname = (gwmi win32_computersystem).name
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
$dt = get-date -format "MMddyy-hhmm"
$Instances = Get-Item "HKLM:\software\microsoft\microsoft sql server\instance names\sql"
foreach($InstanceName in $Instances.property)
{
$body =@()
$errorstate = 0
$InstancePath = $Instances.GetValue($InstanceName)
if(Test-Path ("HKLM:\software\microsoft\microsoft sql server\" + $InstancePath + "\cluster"))
{ #is cluster
$ServerName = (gp ("HKLM:\software\microsoft\microsoft sql server\" + $InstancePath + "\cluster")).ClusterName
}
else #not cluster
{
$ServerName = $hname
}
if($InstanceName -eq "MSSQLSERVER")
{#default Instance
$InstConn = $ServerName
}
else
{#named instance
$InstConn = $ServerName + "\" + $InstanceName
}
$sql = New-Object Microsoft.SqlServer.management.Smo.Server $InstConn
$backuppath += $sql.name + "\"
$backuppath += $sql | ?{$_.instancename -ne "" -or $_.instancename -ne "MSSQLSERVER"} | %{$_.instancename + "\"}
$dbs = $sql.databases | ? {!$_.isSystemObject -And $_.recoverymodel -eq [microsoft.sqlserver.management.smo.recoverymodel]::full}
foreach ($db in $dbs)
{
$path = $backuppath + $db.name + "\"
#clean old trans logs
$lwt = (gci $path *.bak |sort lastwritetime | select -Last 1).lastwritetime
gci $path $.trn | where {$_.lastwritetime -le $lwt} | ri -force
if(!(Test-Path $path)){mkdir $path | Out-Null}
$conn = New-Object Microsoft.SqlServer.management.Smo.Server $InstConn
$bk = new-object microsoft.sqlserver.management.smo.backup
$bk.BackupSetDescription = "log backup of $($db.name) at $(get-date)"
$bk.action = [microsoft.SqlServer.management.smo.backupactiontype]::log
$bk.BackupSetName = "log backup"
$bk.database = $db.name
$bk.Devices.AddDevice("$backuppath$($db.name)\$($db.name)-$dt.trn",'File')
try{
$msg = "$($bk.database) completed successfully"
$bk.sqlbackup($sql)}
catch{$errorstate = 1; $msg = "$($bk.database) Failed : $_"}
$body += $msg
}
if($errorstate -eq 1){$subject = "Log Failure on $hname"} else {$subject = "Log Success on $hname"}
Send-MailMessage -Subject $subject -BodyAsHtml ([string]::join("<br>",$body)) -From $alertaddress -To $alertaddress -SmtpServer $smtp
}