I had a unique problem and spent the last several weeks working on a script to resolve that problem. We have a client whose servers, SQL databases, and websites we are responsible for maintaining. The servers that are running everything are hosted. Our problem was, that there was no local copy of the database for backup, testing, and staging. So, my mission was to get the databases backed up offsite. My challenge was that one of these databases is 30+ GB. That is a lot of file to move over the wire. Luckily we have a VPN connection established between the two sites so I did not have to worry about security for this file transfer. If time permits, I may re-do this script with SFTP, but for now FTP will have to suffice.
I chose 7zip for my zipping and chunking because it was the easiest utility with the smallest footprint, and I got it to work via PowerShell.
I had every intention of keeping a list of my sources for this blog, but unfortunately due to the size of the database and my limited time in which to test, I lost track of all of the sites I used to put all of the pieces together that are necessary for this script. PLEASE, if you see something in this script that I took from one of your scripts (or forum responses), please leave a comment and I will happily give you credit where credit is due.
PLEASE NOTE: You have to place the PowerShell script in a completely separate folder from the files you’re processing. I did not write logic into this script to exclude .ps1 files from processing. I chose a self-describing folder: C:\DBFileProcessScript for the script and log files.
Here is the script with details surrounding what each portion of the script does:
<#
.SYNOPSIS
Zips up files and transfers them via FTP.
.DESCRIPTION
Searches the ‘DBBackup’ folder for all files older than two weeks with
the file extension .bak and moves them to a ‘Process’ folder. It then
moves all other files to a separate folder for cleanup. It then zips
the files and breaks them up into 100MB chunks for more reliable FTP file
transfer. Checks for any thrown errors and emails those errors once the
script finishes.
.NOTES
File Name : Zip_FTP_DBFiles.ps1
Author : Matt Richardson
Requires : PowerShell V2
First, we need to clear the error log.
#>
$error.clear()
#This portion of the script moves the files from the DBBackup folder to the
#Process folder if the file is more than two weeks old. It also moves the .trn
#and .txt files to a separate folder for cleanup later.
foreach ($i in Get-ChildItem C:\DBBackup\*.bak)
{
if ($i.CreationTime -lt ($(Get-Date).AddDays(–13)))
{
Move-Item $i.FullName C:\DBBackup\Process_Folder
}
}
foreach ($i in Get-ChildItem C:\DBBackup\*.t*)
{
if ($i.CreationTime -lt ($(Get-Date).AddDays(–13)))
{
Move-Item $i.FullName C:\DBBackup\Old_TRN_Logs
}
}
#This portion of the script sets the variables needed to zip up the .bak files
# using 7zip. The file query portion of this section makes sure you’re not
# accidentally getting anything other than the .bak files in the event someone
# puts other files in this folder.
$bak_dir = “C:\DBBackup\Process_Folder”
$file_query = “*.bak”
$archivetype=“zip”
#Alias for 7-zip – needed otherwise you get Parse Errors. I had to move the 7z.exe
# file to both the Program Files and Program Files(x86) folders for this to work.
# I know I could have probably noodled with the script a bit more so that this
# wasn’t required, but I haven’t gotten around to that.
if (-not (test-path “$env:ProgramFiles\7-Zip\7z.exe”)) {throw “$env:ProgramFiles(x86)\7-Zip\7z.exe needed”}
set-alias sz “$env:ProgramFiles\7-Zip\7z.exe”
#Change the script so that is running in the correct folder.
cd $bak_dir
#This section chunks up the files and then deletes the original file. I had to do
# the removal for lack of space issues. I would recommend moving this part to the
# end assuming you have space.
$files=get-childitem . $file_query | where-object {!($_.psiscontainer)}
ForEach ($file in $files)
{
$newfile = ($file.fullname + “.$archivetype”)
sz a -mx=5 -v100m ($file.fullname + “.$archivetype”) $file.fullname
Remove-Item $file
}
#This cleans up the tran and txt logs since we’re not copying them offsite.
Remove-Item c:\DBBackup\Old_TRN_Logs\*.t*
#This portion of the script uploads the files via FTP and tracks the progress,
# moving the failed files to a separate folder to try again later. The try
# again later part is yet to be written so for now I do it manually on failure.
foreach ($i in Get-ChildItem “C:\DBBackup\Process_Folder”)
{
$file = “C:\DBBackup\Process_Folder\$i”
$ftp = “ftp://username:password@ftp.server.com/$i”
“ftp url: $ftp”
$webclient = New-Object System.Net.WebClient
$uri = New-Object System.Uri($ftp)
“Uploading $File…”
$webclient.UploadFile($uri, $file)
$? | Out-File -FilePath “c:\DBFileProcessScript\$(get-date -f yyyy-MM-dd).txt” -Append
if ($? -ne “True”)
{
Move-Item $file c:\DBBackup\Retry
}
else
{
continue
}
}
#This portion cleans up the process folder.
Remove-Item c:\DBBackup\Process_Folder\*
#This portion sends an email with the results and any errors.
send-mailmessage -to “alerts@company.com” -from “report@company.com” -subject “File Transfer Complete” -body
“The weekly file transfer of the Database files has completed. If there were errors, they are listed here: $Error”
-smtpserver smtp.company.com
My next challenge was that this job had to run on a schedule. Since it takes approximately 5-6 hours to zip and transfer 30GB worth of database, I obviously wanted to run it during off-hours. I compiled it into an .exe and scheduled it to run at midnight using Task Manager. Unfortunately, the SQL backups were also set to run at midnight, and this script trying to run at the same time as the backups caused the server to lock up and go offline for about 20 minutes. I figured I could safely schedule it for 3 or 4 a.m., but I wanted it to start as soon as possible. So, I write a TSQL script to call this one and edited the maintenance job in SQL to run the PowerShell script upon completion of the backups. This gave me two advantages. One is that it would run immediately after backups were complete maximizing my off-hours time. Two was that if for any reason the backups failed, it wouldn’t run and delete transaction logs and clean up files that may still be needed after a failed backup.
Here is the TSQL Script I found and modified:
EXEC
sp_configure
‘show advanced options’, 1
GO
— To update the currently configured value for advanced options.
RECONFIGURE
GO
— To enable the feature.
EXEC sp_configure ‘xp_cmdshell’, 1
GO
— To update the currently configured value for this feature.
RECONFIGURE
GO
EXEC xp_cmdshell ‘powershell.exe -Command “C:\DBFileProcessScript\Zip_FTP_DBFiles”‘
As I am relatively new to TSQL scripts, I honestly don’t know if the first four commands are necessary to execute every time, but I don’t think it would hurt to re-apply them every time even if it is a persistent setting.
Next is the script to rehydrate the files on the far end. I’ll post that once I am finished with it.
Azure Jacket 