[sims] PowerShell Backup Script

[Chuckster]

I saw the thread http://www.edugeek.net/forums/mis-systems/171205-scripted-backup-sims-fms.html#post1465637 and decided to build upon the code that @Boredguy posted.

 

As I am no PowerShell guru I'm sure the code below could be done better.

 

I have tried and tested it, and it works for me.

 

Change the relevant user and password details to match your environment.

 

The script will create .bak files. Once created, 7-Zip will compress individual files to .7z. Further along the script it then deletes any *.bak files and all the remaining files older than 10 days, and then mirrors the directory to a NAS box. You will need to install 7-Zip.

 

 

######################################################
# Declare Variables
$Date = (Get-Date -Format dd.MM.yyyy_HH.m)
######################################################


######################################################
# SIMS Backup Variables
$BackupPath = "C:\backups"
$SAUsername = "SA"
$SAPassword = "Password"
$DBServer = "simsserver\sims2012"
$DBAttach = "C:\Program Files\Microsoft SQL Server\MSSQL11.SIMS2012\MSSQL\Binn\DbAttach.exe"
######################################################


######################################################
# Backup SQL Databases
Write-Host "Performing Backup of SQL Databases..."
Import-Module SQLPS -DisableNameChecking
Invoke-Sqlcmd -ServerInstance $DBServer -Username $SAUsername -Password $SAPassword -Query "exec sims.sims.db_p_transfer_login"
Start-Sleep -s 30
Invoke-Sqlcmd -ServerInstance $DBServer -Username $SAUsername -Password $SAPassword -Query "exec fms.sims.db_p_transfer_login"
Start-Sleep -s 30

&$DBAttach /BACKUP /AUTO /SERVER=$DBServer /DATABASE=SIMS /USER=$SAUsername /PASSWORD=$SAPassword /PATH=$BackupPath\SIMS_$Date.bak | Out-Host
&$DBAttach /BACKUP /AUTO /SERVER=$DBServer /DATABASE=FMS /USER=$SAUsername /PASSWORD=$SAPassword /PATH=$BackupPath\FMS_$Date.bak | Out-Host
######################################################


######################################################
# 7-Zip Variables
$filePath = "C:\backups"
$bak = Get-ChildItem -Recurse -Path $filePath | Where-Object { $_.Extension -eq ".bak" }

if (-Not (Test-Path "C:\Program Files\7-Zip\7z.exe")) {throw "7-Zip needed"} 
set-alias 7z "$env:ProgramFiles\7-Zip\7z.exe"

# Loop through the files and add the .bak files to a .7z compressed file
Write-Host "Compressing *.BAK to *.7z"
foreach ($file in $bak) { 
   $name = $file.name 
   $directory = $file.DirectoryName 
   $zipfile = $name.Replace(".bak",".7z") 
   7z a -t7z "$directory\$zipfile" "$directory\$name" 
}
######################################################


######################################################
# Delete *.Bak Files
Write-Host "Removing *.BAK files..."
Get-Childitem $BackupPath -Include *.bak -Recurse -Force | foreach ($_) {remove-item $_.fullname}
######################################################


######################################################
# Deletion Variables
$Age = -10
#Path to Root Folder
$Path = "C:\backups"

# Delete files within the root folder older than 10 Days
Write-Host "Deleting files  older than 10 Days..."
Get-Childitem $Path -Recurse | Where {$_.lastwritetime -lt (Get-Date).adddays($Age) -and -not $_.psiscontainer} |% {remove-item $_.fullname -Force}
######################################################


######################################################
# Robocopy Variables
$Source = "C:\backups"
$Destination = "Z:"
$Switches = ("/MIR", "/COPY:DT", "/R:5", "/W:10")

# Robocopy 
Write-Host "Mirroring SQL Database Backups to Remote Backup Server"
NET USE Z: \\RemoteServer\SIMS_FMS_Backup$ /PERSISTENT:No /USER:RemoteServer\Admin P@ssw0rd
ROBOCOPY $switches $Source $Destination
NET USE Z: /DELETE /Y
######################################################

Edited June 13, 2016 by Chuckster

[matt40k]

Few comments - put code on GitHub - gist are excellent for scripts like this - https://gist.github.com/. All code should be in version control. Period.

 

Not sure why you've mapped a network drive - robocopy will copy across UNC paths, also wouldn't you use Win Auth rather then basic auth, ie don't pass the password, just the token

put the backup variable, along with all variables at the top

 

Have you looked at my tidybackups tool? It does the compression (only as a zip) but the key feature is the preserve - it will ALWAYS keep at least a set number of backups, so if you dbattach stops making backups you won't end up deleting your only backup.

 

Have you looked at adding something for Discover? Or the SOLUS3 database? Or the DocStorage? Or the SIMS share (where the legacy stuff lives)?

 

Nice start!

[Boredguy]

Have you looked at adding something for Discover? Or the SOLUS3 database? Or the DocStorage? Or the SIMS share (where the legacy stuff lives)?

 

Nice start!

Our own script, which @DJ-1701 did 95% of the work on, backs up S3, Discover, DocStorage and the SIMS Share for us.

The basic PS code for backing up the main SIMS DB's was posted as originally that was all someone wanted

[DJ-1701]

As I have been asked what the differences are from my script to @Chuckster's script... after harmonising and reviewing our coding, not much difference at all.

 

Basically we have the two additional databases (Solus3 and Discover) backed up:

 

&$DBAttach /BACKUP /AUTO /SERVER=$DBServer /DATABASE=Solus3_deployment_server /USER=$SAUsername /PASSWORD=$SAPassword /PATH=$BackupPath\SOLUS.BAK | Out-Host
&$DBAttach /BACKUP /AUTO /SERVER=$DBServer /DATABASE=Discover /USER=$SAUsername /PASSWORD=$SAPassword /PATH=$BackupPath\Discover.BAK | Out-Host

 

As well as this we have two additional robocopy commands, one for the directory Simsdata (i.e. the SIMS Share) and another for the Docstorage directory, both going to the backup server.

Edited July 25, 2016 by DJ-1701