SQL database backup to Citrix ShareFile


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


Leave a comment

Your email address will not be published. Required fields are marked *