Creazione e compressione backup database SQL SERVER EXPRESS tramite script in Powershell

Lo script che segue permette di effettuare un backup di un database di SQL EXPRESS, compattarlo e creare un file di log.

Requisiti:

- SQL Server distribuzione EXPRESS (qui il link)

- 7-z (qui il link)

- Powershell

Istruzioni:

Installato e configurato SQL Server express e 7-z, procediamo nella creazione dello script, quest'ultimo è così suddiviso:

1 - Connessione al database del quale si vuole effettuare il backup.

2 - Creazione del backup del database (così creato "nomedb-giornomeseannooraminutisecondi.bak")

3 - Compressione e controllo del file (così creato "nomedb-giornomeseannooraminutisecondi.zip")

4 - Creazione del file di log riportante l'esito della compressione (così creato "nomedb-giornomeseannooraminutisecondi.txt")

5 (opzionale) - Invio mail con allegato il file di log

6 - Cancellazione del file di backup

7 (opzionale) - Cancellazione files più vecchi di X giorni

8 - Controllo spazio su disco ed invio mail di avviso

Aperto un editor di testo (anche il notepad va bene), incolliamo le seguenti stringhe:

#Dichiarazione variabili
 
$ServerName = "nome_server\istanza_SQL"
$BackupDirectory = "cartella salavataggio dati"
$dbToBackup = "nome database"

#pulizia schermo

cls

#connessione a SQL Server Management Object
 
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null


#definizione variabili
$server = New-Object ("Microsoft.SqlServer.Management.Smo.Server") $ServerName

"Default Backup Directory: " + $backupDirectory 


$db = $server.Databases[$dbToBackup]
$dbName = $db.Name
$timestamp = Get-Date -format yyyyMMdd-HHmmss
$smoBackup = New-Object ("Microsoft.SqlServer.Management.Smo.Backup")

#esecuzione backup

$smoBackup.Action = "Database"
$smoBackup.BackupSetDescription = "Full Backup of " + $dbName
$smoBackup.BackupSetName = $dbName + "Backup"
$smoBackup.Database = $dbName
$smoBackup.MediaDescription = "Disk"
$smoBackup.Devices.AddDevice($backupDirectory + "\" + $timestamp + "_" + $dbName + ".bak", "File")
$smoBackup.SqlBackup($server)
 
#compressione del file di backup

if (-not (test-path "$env:ProgramFiles\7-Zip\7z.exe")) {throw "$env:ProgramFiles\7-Zip\7z.exe needed"}
set-alias sz "$env:ProgramFiles\7-Zip\7z.exe"  
$Source = ($backupDirectory + $timestamp + "_" + $dbName + ".bak")
$Target = ($backupDirectory + $timestamp + "_" + $dbName + ".zip")
$logFile = ($backupDirectory + $timestamp + "_" + $dbName + ".txt")

sz a -mx=9 $Target $Source  | set-content $logFile -encoding ascii
 
#cancellazione il file di backup

Remove-Item $Source -recurse

#invio email con allegato il log
 
#$emailFrom = "indirizzo e-mail mittente"
#$emailTo = "indirizzo e-mail destinatario"
#$subject = "Oggetto della mail"
#$body = "Testo della mail "
#$smtpServer = "indirizzo server smtp"
#$filePath = $logFile
#Function sendEmail([string]$emailFrom, [string]$emailTo, [string]$subject,[string]$body,[string]$smtpServer,[string]$filePath)
#{
#creazione e-mail
#$email = New-Object System.Net.Mail.MailMessage
#$email.From = $emailFrom
#$email.To.Add($emailTo)
#$email.Subject = $subject
#$email.Body = $body
# initiate email attachment
#$emailAttach = New-Object System.Net.Mail.Attachment $filePath
#$email.Attachments.Add($emailAttach)
#initiate sending email
#$smtp = new-object Net.Mail.SmtpClient($smtpServer)
#$smtp.Send($email)
#}

#avvio servizio spedizione e-mail
#sendEmail $emailFrom $emailTo $subject $body $smtpServer $filePath

#eliminazione di file più vecchi di 7 gg

$Daysback = "-inserire il numero dei giorni"
$CurrentDate = Get-Date
$DatetoDelete = $CurrentDate.AddDays($Daysback)
#no sotto cartelle
Get-ChildItem $BackupDirectory | Where-Object { $_.LastWriteTime -lt $DatetoDelete } | Remove-Item

#Visualizza il contenuto della directory


$activity = "Avvio creazione file compressi e relativi log"

$status = "Avvio"
for($counter = 0; $counter -lt 100; $counter++)
{
    $currentOperation = "Percentuale completamento $counter"
    Write-Progress $activity $status -PercentComplete $counter `
                   -CurrentOperation $currentOperation
    Start-Sleep -m 130
}

 Get-ChildItem -Path $BackupDirectory

#controllo spazio su disco ed eventuale invio mail

$min = 5;  #soglia per l'avviso in GB
 
$warningString = "* Warning < $min GB"
$smtpServer = "indirizzo server smtp"
$emailFrom = "indirizzo e-mail mittente"
$emailTo = "indirizzo e-mail destinatario"
$subject = "Report Disk Space ({0})" -f $env:computername

$minByte = $min * 1GB
$drives = Get-WmiObject  Win32_LogicalDisk | Where-Object {$_.DriveType -eq 3}

$frmt = @{l="Drive";e={"{0}" -F ($_.DeviceID)};width=10},`
 @{l="Message";e={if($_.freespace -lt $minByte) {$warningString} else {"OK"}};width=20}, `
 @{l="Free %";e={"{0:N2}" -f ([long]$_.FreeSpace / [long]$_.Size * 100)};width=10}, `
 @{l="Free GB";e={"{0:N2}" -f ($_.FreeSpace / 1GB)};width=10}, `
 @{l="Size GB";e={"{0:N2}" -f ($_.Size / 1GB)};width=10}
 
#mostra una barra di avanzamento   

$result = $drives | Format-Table $frmt | out-string

if($result.Contains($warningString) )
{
 $body = "Server: {0} - Date: {1:dd MMM yyyy HH:mm:ss} `r`n $result `r`n `r`n-- End Report --" -f $env:computername, [DateTime]::Now

 #send email
 $smtp = new-object Net.Mail.SmtpClient($smtpServer)
 $smtp.Send($emailFrom, $emailTo, $subject, $body)

 write-host $body
} 

Ora salvare il file con estensione ".ps1", avviare (come ammistratore) powershell ed avviare lo script sopra creato.