BackupSQLDatabases/BackupSQLToAzureBlob.ps1 at main · TuCje/BackupSQLDatabases · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
# Setting the mailserver Variables
$From = ""
$To = ""
$Bcc = ""
$SMTPServer = ""
$Body = "Backup succesful, see below: <br> "
$Subject = ""
$MailUsr = ""
$Port = ""
$MailPwd = ""| ConvertTo-SecureString -AsPlainText -Force
$MailCred = New-Object System.Management.Automation.PsCredential($MailUsr,$MailPwd)

#Set the current Date
$Datum = get-date -format "dd-MM-yyyy-HH-mm"

#Setting up the Drive to Azure under the account the script is running
$pass="$blobpass"| ConvertTo-SecureString -AsPlainText -Force
$Cred = New-Object System.Management.Automation.PsCredential('localhost\$bloblogin',$pass)
New-PSDrive -name W -Root -Credential $Cred -PSProvider filesystem -ErrorAction SilentlyContinue

# Get the databases
$DatabasesInstance1 = Get-SqlDatabase -ServerInstance $serverinstance1

function MakeBackup {
    param (
        $serverinstance,
        $SQLdatabase
    )
    Backup-SqlDatabase -ServerInstance "$serverinstance" -Database "$database" -BackupFile "$location\DB-$serverinstance1-$database-$Datum.bak"
    Backup-SqlDatabase -ServerInstance "$serverinstance" -Database "$database" -BackupFile "$location\LOG-$serverinstance1-$database-$Datum.bak" -BackupAction Log


}


# Make the backups of Databases in Instance1
ForEach ($database in $DatabasesInstance1) {
#Make the backup and store in file
Backup-SqlDatabase -ServerInstance "$serverinstance1" -Database "$database" -BackupFile "$location\DB-$serverinstance1-$database-$Datum.bak"
Backup-SqlDatabase -ServerInstance "$serverinstance1" -Database "$database" -BackupFile "$location\LOG-$serverinstance1-$database-$Datum.bak" -BackupAction Log
#zip the files
v:
cd..
cd 7zip
cmd.exe /c "7z.exe a -t7z $location\$serverinstance1-$database-$Datum.7z -m0=lzma2 -mx0 -slp $location\DB-$serverinstance1-$database-$Datum.bak $location\LOG-$serverinstance1-$database-$Datum.bak"

[int]$BackupSize = "{0:N2}" -f ((gci -force $location\$serverinstance1-$database-$Datum.7z -Recurse -ErrorAction SilentlyContinue| measure Length -s).sum / 1Mb)
    if ($BackupSize -gt 1){

       Copy-Item -Path $location\$serverinstance1-$database-$Datum.7z -Destination W:\Backups\$serverinstance1-$database-$Datum.7z -Force
       [int]$BackupSizeAz = "{0:N2}" -f ((gci -force W:\Backups\$serverinstance1-$database-$Datum.7z -Recurse -ErrorAction SilentlyContinue| measure Length -s).sum / 1Mb)

            if ($BackupSizeAz -eq $BackupSize7z){

                $Body += "$serverinstance1-$database-$Datum.7z Succeeded. Size in Mb: $BackupSize7z<br>"
                Remove-Item "location\$serverinstance1-$database-$Datum.7z" -Confirm:$false -Recurse
                Remove-Item "$location\DB-$serverinstance1-$database-$Datum.bak" -Confirm:$false -Recurse
                Remove-Item "$location\LOG-$serverinstance1-$database-$Datum.bak" -Confirm:$false -Recurse

            }else{

                    $Body += "$serverinstance1-$database-$Datum.7z NOT Succeeded to copy. Size is: $BackupSize7z<br>"
            }

    }else{

        $Body += "$serverinstance1-$database-$Datum.7z NOT Succeeded. Size is less than 1Mb. Size is: $BackupSize7z<br>"

       }
}

Send-MailMessage -From $From -To $To -Bcc $Bcc -Subject $Subject -Body $Body -BodyAsHtml -SmtpServer $SMTPServer -Credential $MailCred -Port $Port