I love VirtualBox. However, I don’t like how big those files are and how disaster can strike any time.
This is exactly what happened to me before my Christmas break. All my pet projects were on a VirtualBox machine (OS installed) stored on an external drive, and it died!!
It really felt like someone near to me passed away. I tried to recover and even thought of getting professional help. However, the quote I received for recovering the files made me realize that I’m a miser (the quote was anywhere between $954 – $4770) 🙂
The sad part was my vacation was spend (long night) rebuilding those pet projects.
In this video were are going to look at how I managed to back up my OS SQL Server databases to a Citrix Sharefile folder.
Code
hwr
Add-PSSnapin ShareFile
# I'm using the default SQL instance
$SQLInstance = "."
# Thus is the default backup location for SQL server
$SQLBackupFolder = "C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\"
$timeStamp = Get-Date -format yyyy_MM_dd_HHmmss
# Delete all files that are older than 1 day from the backup folder
$Daysback = "-1"
$CurrentDate = Get-Date
$DatetoDelete = $CurrentDate.AddDays($Daysback)
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
$srv = New-Object ("Microsoft.SqlServer.Management.Smo.Server") $SQLInstance
$dbs = New-Object Microsoft.SqlServer.Management.Smo.Database
$dbs = $srv.Databases
foreach ($Database in $dbs)
{
# check if this is a system database
if (-Not $Database.IsSystemObject) {
$bk = New-Object ("Microsoft.SqlServer.Management.Smo.Backup")
$bk.Action = [Microsoft.SqlServer.Management.Smo.BackupActionType]::Database
$bk.BackupSetName = $Database.Name + "_backup_" + $timeStamp
$bk.Database = $Database.Name
$bk.CompressionOption = 1
$bk.MediaDescription = "Disk"
$bk.Devices.AddDevice($SQLBackupFolder + $Database.Name + "_" + $timeStamp + ".bak", "File")
TRY {
$bk.SqlBackup($srv)
#Compress backup file
$fileName = $SQLBackupFolder + $Database.Name + "_" + $timeStamp + ".bak"
$zipFileName = $SQLBackupFolder + $Database.Name + "_" + $timeStamp + ".zip"
Compress-Archive -LiteralPath $fileName -DestinationPath $zipFileName
#delete .bak files
Remove-Item -Path $fileName -Force
write-output "$CurrentDate $Database backup was successful."
}
CATCH
{
$Database.Name + " backup failed."
$_.Exception.Message
}
}
}
# Read Sharefile Client
$sfClient = Get-SfClient -Name "C:\OSTools\MSASharefile.sfps"
#Set a sharefile drive
New-PSDrive -Name sfDrive -PSProvider ShareFile -Root / -Client $sfClient | Out-Null
# Set the sharefile home folder of the user
$homeFolder = "sfDrive:/" + (Send-SfRequest $sfClient -Entity Items).Name
# set the backup folder for SQL
$sfSQLBackupFolder = $homeFolder + "/SQLBackups"
#upload all the files in the local SQLBackupFolder to the specified SQLBackupFolder in ShareFile
#Copy-SfItem -Path ($SQLBackupFolder + '\*') -Destination $sfSQLBackupFolder
# Copy backup files one by one
Get-ChildItem $SQLBackupFolder | ForEach-Object -Begin $null -Process { Copy-SfItem -Path ($_.FullName) -Destination $sfSQLBackupFolder }, { $_.Name + " copied to ShareFile" } -End $null
if($?)
{
"Copy to ShareFile finished"
# Remove all items from local
Get-ChildItem $SQLBackupFolder | Remove-Item
# Remove items that are older
Get-ChildItem "S:\Personal Folders\SQLBackups" | Where-Object { $_.LastWriteTime -lt $DatetoDelete } | ForEach-Object { Remove-Item -LiteralPath $_.FullName }
}
else
{
"Copy to ShareFile failed"
}
cd C:
Remove-PSDrive sfDrive