SQL Backup using Powershell Script

SQL Backup using Powershell Script

 Take SQL Server database backup using powershell script....

1. Create SQL Server database backup 
2. Compress backup file in zip file
3. Upload backup zip file on Azure blob storage 

cls
#Set Variables
$Server = "localhost"
$Database = "database-name"
$BackupBaseFolder = "D:\backup\"
$BackupFolder = "D:\backup\"
$dt = get-date  -format MM-dd-yyyy-hh-mm-ss
$directoryName = "$($Database)_db_$($dt)"
$BackupFolder = "$($BackupFolder)$($directoryName)\"
$FilePath = "$($BackupFolder)$($Database)_db_$($dt).bak"

#Create backup folder 
md $BackupFolder
 
#Call SQL Command
Backup-SqlDatabase -ServerInstance $Server -Database $Database -BackupFile $FilePath 

#Create zip file
$source = $BackupFolder
$directoryPath = Join-Path $BackupFolder $directoryName
$destination = Join-Path $BackupBaseFolder "$directoryName.zip" 

If(Test-path $destination) {Remove-item $destination}
Add-Type -assembly "system.io.compression.filesystem"
[io.compression.zipfile]::CreateFromDirectory($Source, $destination) 

#Get files list from folder
$folders = Get-ChildItem $BackupBaseFolder -Directory
foreach($folderName in $folders)
{  
 $folder = $BackupBaseFolder + "\" + $folderName
 Remove-Item -recurse -force $folder #-erroraction silentlycontinue  
}

#Upload Backup Script on Azure blob storage

$localFileDirectory = "D:\backup\"
$StorageAccountName = "storageAccountname" 
$StorageAccountKey = "AccountKey"
$ContainerName = "ContainerName"

$ctx = New-AzureStorageContext -StorageAccountName $StorageAccountName -StorageAccountKey $StorageAccountKey

#Create container 
#New-AzureStorageContainer -Name $ContainerName -Context $ctx -Permission Blob
 
#Get files list from folder
$files = Get-ChildItem $localFileDirectory *.zip

#process each file
foreach($fileName in $files)
{  
 $localFile = $localFileDirectory + $fileName
 
 $azblob = Get-AzureStorageBlob -Blob $fileName -Container $ContainerName -Context $ctx -ErrorAction SilentlyContinue
 
 If ($azblob -ne $null) {
            # Blob already exists, check the lastwrite metadata
            Write-Host $fileName " file exists"
        } Else {
            
            #Upload file in storage
   Set-AzureStorageBlobContent -File $localFile -Blob $fileName -Container $ContainerName  -Context $ctx  -Force -ErrorAction SilentlyContinue
   #Remove file after upload file
   Remove-Item $localFile
        }
}

Write-Host "Backup file uploaded on storage..."
 

Hope this will help you and save your time.

Enjoy !!!

:)