#Scott Alvarino
#01/28/2011 Version 3
#This is script to backup Sybase db to 2 other different locations.
#It also notifies the user through email, plus leaves a syslog message and a winEvent message.
#add-pssnapin NetCmdlets # Remove the comment if pssnapin is not in the PS profile
#Variables Section That is configureable
$ftptimeframemin =”-8change_me” #How many minutes back do you want the file to be copied over so that it does not copy everything.
$DeleteAfterMonths = “-15change_me” # How many months to go back before deleting the old dump files
$maindump = “/scott_backups/UAS_BACKUPS/” #Unix Server Directory to save db Dump File
$server = “10.128.2.change_me” # Put Sybase Server here
$db = “change_me” # Put DB to backup name here
$dsn = “change_me” # Put DSN name here
$port = “9100change_me” #Put port number for sybase server here
$sybaselogin = “sachange_me” #Put Sybase login here
$sybasepw = “change_me” #Put Sybase User pw – make sure script is in encrypted file system with correct user access
$user = “change_me” #Put UAS UnixID pw
$emailUser = “change_me” #Put email User
$pw = “change_me” #Put email User PW
$too = “change_me” #Put email of recipient of notification
$cc = “change_me” #Put Carbon Copy of another recipient to be notified
$from = “Automated Backup<change_me@miami.gov>” #Put the From email address
$subject = “**UAS SUCCESSFULL BACKUP**” #Subject of Email
$mailserver = “smtp.gmail.comchange_me” # Email server remember if not gmail place a comment# in front of the -UseSSL parameter
$syslogserver = “aplserver003change_me” # Name of Syslog Server
$remotefile = “/change_me/UAS_BACKUPS/UAS_Backup-*” #Put remote location with wildcard to ftp file
$localfile = “S:\change_me\uas” #Put the directory you want to save file to
$CopyLocalfileto = “F:\change_me\” #Copy the file to another directory on a different hardrive to have 2 copies
$body = “Goodmorning,
The UAS has been backed up.
Here is the folder it was put in \\change_me\!backups\uas
Please let me know if you have any questions.
Thankyou, ”
#Variable Section Not Configureable – OR should not
$date = get-date -uformat “%Y-%m-%d_%H%M” #Get the date back in a workable format
$dumpfolder =$maindump+UAS_Backup-+$date+”_.dmp” #Prepare dump file folder and name
$dest = $CopyLocalfileto+$date #Copies dump file to another Drive
$Min = (get-date).AddMinutes($ftptimeframemin) #How many minutes back do you want the file to be copied over so that it does not copy everything.
$YearBack = (get-date).AddMonths($DeleteAfterMonths) # How many months to go back before deleting the old dump files
$facility = 1
$Severity = 6
$eventid = “6969″
$eventtype = “Information”
$logname = “Application”
$eventsource = “Desktop Window Manager”
$sysMessage = “UAS DB SUCCESSFULLY BACKEDUP at $(get-date)!!”
$query=”DECLARE @BACKUPNAME varchar(100) set @BACKUPNAME = ‘$dumpfolder’ dump database $db to @BACKUPNAME”
#Setting Up My PSCRedentials###############################################################
$secpasswd = ConvertTo-SecureString $pw -AsPlainText -Force
$mycreds = New-Object System.Management.Automation.PSCredential($emailUser, $secpasswd)
####################################################################################
$conn=New-Object System.Data.Odbc.OdbcConnection
$conn.ConnectionString= “driver={Adaptive Server Enterprise}; dsn=$dsn;db=$db;na=$server,$port;uid=$sybaselogin;pwd=$sybasepw;”
$conn.open()
$cmd=new-object System.Data.Odbc.OdbcCommand($query,$conn)
$cmd.CommandTimeout=0
$ds=New-Object system.Data.DataSet
$da=New-Object system.Data.odbc.odbcDataAdapter($cmd)
$da.fill($ds)
$ds.Tables[0] #| format-table name,ID
$conn.close()
#this part FTPs the file to the server for backing up and deletes the file from the server
get-ftp -server $server -user $user -password $pw -binary -remotefile $remotefile -localfile $localfile
if($?)
{remove-ftp -server $server -user $user -password $pw -remotefile $remotefile }
If (Test-Path -Path $localfile) {
new-item -path $dest -type directory
$Container = ls $localfile | ? {!$_.psiscontainer}
ForEach ($file in $Container) {
IF ($file.creationtime -gt $Min) {
cp -Path $file.fullname -destination $dest -recurse
}
Else {IF ($file.creationtime -lt $YearBack){
rm -Path $file.fullname -force }
}
}
}
if($?){
#Send email using send-mailmessage
send-mailmessage -to $too -from $from -subject $subject -body $body -smtpserver $mailserver -Cc $cc -credential $mycreds -UseSSL
}
if($?){
#Send to Syslog
send-syslog -server $syslogserver -facility $facility -Severity $Severity -message $sysMessage
#Write a WinEvent
write-eventlog -logname $logname -source $eventsource -eventid $eventid -entrytype $eventtype -message $sysMessage
}
