Simple Robocopy Backup Script

This simple backup script will copy a folder to another server using dated folders and delete old backup folders older than the specified number of days (15 in this example). Add this script to the task scheduler. In an AD environment, assign rights to the target location to the computer object and run the task as SYSTEM to avoid pesky service accounts.

# Written by Ben Penney $limit = (Get-Date).AddDays(-15) $SourcePath = "c:\folder" $TargetPath = "\\backupnas\Backup\servername" $SMTPRecipients = "","" $SMTPFrom = "" $SMTPServer = "" $SMTPSubject = "Server backup" $EmailBody = "Starting process $(Get-Date)`n" # Delete folders older than the $limit. Get-ChildItem -Path $path -Force | Where-Object { $_.PSIsContainer -and $_.CreationTime -lt $limit } | ForEach { $EmailBody += "Deleting folder "+($_.Name) Remove-Item -Force -recurse } $DateString = (Get-Date).ToString("yyyyMMdd") $RobocopyOutput = RoboCopy $SourcePath $TargetPath\$DateString /COPY:DAT /E /R:2 /W:2 /NFL /NDL /NP # OPTIONAL CODE TO TRUNCATE THE ROBOCOPY OUTPUT If ($RobocopyOutput.Count -gt 200) { $RobocopyOutput = $RobocopyOutput[0..200] $RobocopyOutput[0] = "********** ROBOCOPY LOG TRUNCATED ************" } $EmailBody += [system.string]::join("`n",$RobocopyOutput) If ($EmailBody.Contains("ERROR")) {$SMTPSubject += " ***ERRORS***"} $EmailBody += "`n`nFinished process $(Get-Date)" Send-MailMessage -To $SMTPRecipients -From $SMTPFrom -Subject $SMTPSubject -SMTPServer $SMTPServer -Body $EmailBody

Using Powershell to read and write MSSQL

These examples are using a test database called EmailList with a table called Table1 which has three fields ‘username’,’email’ and ‘display name’.

This sample code will log in to MSSQL with your current logged in credentials,

$dataSource = "TESTSQLSERVER" $database = "EmailList" $connectionString = "Server=$dataSource;Database=$database;Integrated Security=True;" $connection = New-Object System.Data.SqlClient.SqlConnection $connection.ConnectionString = $connectionString $connection.Open() $command = $connection.CreateCommand()

This code will return the number of records with the specified email address,

$command.CommandText = "Select Count(*) FROM Table1 Where Email=''" $command.ExecuteScalar()

This code with write a record to the database,

$command.CommandText = "INSERT INTO Table1 VALUES ('username','','Users Name')" $command.ExecuteNonQuery()

This code will read records from the database,

$command.CommandText = "SELECT * FROM Table1 WHERE Email=''" $reader = $command.ExecuteReader() While ($reader.Read()) {$reader[0];$reader[1];$reader[2]} $reader.Close()

Don’t forget to close your connection once you have finished,