Lucidtransition.com

A collegue of mine developed the following Windows Powershell script. On the database (MSSQL) server, the script is configured to be run on a weekly basis by a Windows Scheduled Task and takes a CSV file as a command line argument. Within the CSV are all of the database names and corresponding FTP credentials for a given backup. The script reads the CSV line-by-line, creates a SQL dump for each database, then transfers each dump to a specified location on the remote server. Useful for those who provide FTP access to a web server but not direct access to the database server. Very nice.

#clear screen
cls

#import list of databases and Remote FTP information
#CSV file is in the following format
#database,ftp_server,ftp_user,ftp_password,ftp_path
$list = Import-Csv $args[0] #CSV File Name is passed via command line

#Default Path for Database Backups
$backup_path_base = "C:\backups\"

foreach($entry in $list){
	#Database to backup
	$db_to_backup = $entry.database

	#FTP Information
	$remote_server = $entry.ftp_server
	$ftp_username = $entry.ftp_user
	$ftp_password = $entry.ftp_password
	$ftp_path = $entry.ftp_path

	#full path for database backup
	$backup_location = $backup_path_base + "\" + $db_to_backup

	#check to see if the database backup directory exists
	if(Test-Path $backup_location){
		#directory exists...move along
	} else {
		#create missing directory
	 	New-Item -Path $backup_location -type directory | Out-Null
	}

	[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
	[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
	[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null
	[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null

	#create a new server object
	$server = New-Object ("Microsoft.SqlServer.Management.Smo.Server") "(local)"
	$db = $server.Databases[$db_to_backup]
	$db_name = $db.Name

	#generate timestamp for backup file name
	[string]$timestamp = Get-Date -format yyyyMMddHHmmss

	#Set file name of Backup File
	$backup_file_name = $db_to_backup + "_" +$timestamp + ".bak"

	try{
		#Backup the database
		$smoBackup = New-Object ("Microsoft.SqlServer.Management.Smo.Backup")
		$smoBackup.Action = "Database"
		$smoBackup.BackupSetDescription = "Full Backup of " + $db_to_backup
		$smoBackup.BackupSetName = $db_to_backup + " Backup"
		$smoBackup.Database = $db_to_backup
		$smoBackup.MediaDescription = "Disk"
		$smoBackup.Devices.AddDevice($backup_location + "\" + $backup_file_name, "File")
		$smoBackup.SqlBackup($server)

		try{
			# Create a FTPWebRequest
			$ftp_uri = "ftp://" + $remote_server + $ftp_path + "/"
			$webclient = New-Object System.Net.WebClient
			$webclient.Credentials = New-Object System.Net.NetworkCredential($ftp_username,$ftp_password)
			$uri = New-Object System.Uri($ftp_uri + $backup_file_name)
			$webclient.UploadFile($uri, $backup_location + "\" + $backup_file_name)
			Remove-Item $backup_location"\"$backup_file_name
		} catch {
			Write-Host "FTP Error!"
		}
	} catch{
		Write-Host "Error Backing Up Database!"
	}
}