# 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("
",$body)) -From $alertaddress -To $alertaddress -SmtpServer $smtp }