In this example, I create a Windows service that manages Google Drive. The service calls a batch file to create an Oracle backup using EXP, manages space on Google Drive and then uploads the backup to google, finally sending an html email via smtp.
The school of hard knocks is a great teacher. Having a hosting provide fail can be a nightmare. Invariably this happens without notice and suddenly you are in scramble mode and without sleep for days moving. There are ways to hedge the dependency on your hosting provider. One way is to host your DNS with a third party. This way should things go sideways, you can readily change your DNS settings. Another good practice is to keep a scheduled cold backup of your database independent of your hosting provider. Google Drive is a great choice for this. Not only has google made a commitment to putting their servers up on 10gbs, if your servers are on 1gbs or 10gbs copying large files to google is smoking fast. The db using the code in this example creates an 800gb cold backup, it takes under 15 seconds to put up on google drive.
Automating this backup process with google drive and Google Business is a bit convoluted, there are security issues and settings that are simpler with a gmail account, or no associated account. Using Google Business to host your transnational mail is a smart move. No one messes with Google for mail delivery into the inbox. Why struggle with a shared ip or building an ip reputation for transnational mail, marketing mail is a different story, but for transnational mail there is no better choice then Google for Business for a small company. Drive allocation on Google Business, per user, is 30gb vs 15 with gmail.
This examples uses the Google REST API v3, Visual Studio 2012 in C#
Reference https://developers.google.com/drive/v3/web/about-sdk
In a user scenario Google authenticates via a challenge via the browser, unfortunately, that will not work here because we are creating a Service Application, in this case a Windows Service. Services do not have access to an active user session, fortunately, Google anticipated this need and supports offline authentication for service accounts.
Setup a Service Account on Google
Access console.developers.google.com using admin credentials for your business account.
Locate (the every changing) section for Google Apps APIs
Select Drive (formerly google docs)
Enable Google drive from the API Manager.
Select Credentials (left pane)
Create Credentials as a Service Account key using a project name, this project uses "ServerBackup" but you can name it whatever you want just set in the config.
Under permissions, assign the user name user@yourdomain Permission to use "ServerBackups" this is how google knows which user account for drive. From this step you need the internal service-account google created, this is in the form of an email address, it is not the same as a user email address, which is assigned permission to use the service account. These values go in the config file. This is the connection between the internal service account and the user account. It is possible to omit the user account in your code when you create the credentials, the service account is created with 15gb of storage, but there is no way to access the service account other than the api. This is the only way I could this to work was without the user name until I figured out how to associate a user name to the service account. If you do not care, nor need to access what you are uploading via drive.google.com and 15gb is fine, skip it. The final step is to create the p12 key for the service account. Under credentials, select the service account, then manage service accounts, then select create key and download in p12 format. This is the private key, handle as you wish. Because google is forever changing their interfaces, screen shots have the lifespan of a fruit-fly.
To implement, this example creates a basic windows service which calls an assembly.
This example only shows how to create the assembly using VS 2012 and 4.0 of the assembly for x64.
Create a new class library and open the Package Manager Console.
PM>Install-Package Google.Apis.Drive.v3
(only do this for the assembly. not the service exe)
Below is the code:
using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Diagnostics;
using System.Configuration;
using System.IO;
using System.Data;
using System.Net;
using System.Net.Mail;
using System.Threading;
using System.Security.Cryptography.X509Certificates;
using Google.Apis.Auth.OAuth2;
using Google.Apis.Drive.v3;
using Google.Apis.Drive.v3.Data;
using Google.Apis.Services;
using Google.Apis.Util.Store;
namespace GPHousekeepingServer
{
public class SMTPRec
{
public bool processConfirmations;
public string host;
public string hostUserName;
public string hostPassword;
public int hostPort;
public string to;
public string from;
public string htmlSource;
public string rawHTML;
public string bcc;
public string subject;
public bool useSSL;
public SMTPRec(string passedhtml, string passedSubject)
{
try
{
//because this is running from an assembly, cannot use ConfigurationManager directly.
useSSL = false;
string path = System.Reflection.Assembly.GetExecutingAssembly().Location;
Configuration config = ConfigurationManager.OpenExeConfiguration(path);
host = config.AppSettings.Settings["SmtpHost"].Value;
string sPort = config.AppSettings.Settings["SmtpPort"].Value;
if (sPort.Equals(string.Empty))
throw new Exception("SmtpPort is null");
if (!int.TryParse(sPort, out hostPort))
throw new Exception("Unable to convert SmtpPort to an int.");
if (config.AppSettings.Settings["SmtpHostUserName"] != null)
{
hostUserName = config.AppSettings.Settings["SmtpHostUserName"].Value;
if (config.AppSettings.Settings["SmtpHostPassword"] == null)
throw new Exception("SmtpHostUserName set, however SmtpHostPassword is null");
hostPassword = config.AppSettings.Settings["SmtpHostPassword"].Value;
}
if (config.AppSettings.Settings["SmtpUseSSL"] != null)
useSSL = config.AppSettings.Settings["SmtpUseSSL"].Value.Equals("1");
to = config.AppSettings.Settings["ToEmail"].Value;
from = config.AppSettings.Settings["FromEmail"].Value;
bcc = string.Empty;
if (passedSubject.Equals(string.Empty))
subject = config.AppSettings.Settings["subject"].Value;
else
subject = passedSubject;
rawHTML = passedhtml;
}
catch (Exception E)
{
throw new Exception("Exception raised in SMTPRec constructor, " + E.Message);
}
}
}
public static class HouseKeeping
{
private static CollateralClass c = null;
private static Thread thread = null;
public static void Start()
{
try
{
if (c != null)
throw new Exception("Thread is already running.");
c = new CollateralClass();
c.bTerminated = false;
thread = new Thread(new ThreadStart(c.Worker));
thread.Start();
}
catch (Exception E)
{
Utility.SendEventLogError("Fatal error in Housekeeping (stopping), " + E.Message);
}
}
public static void Stop()
{
try
{
if (c == null)
throw new Exception("Thread is not running, call Execute to start thread.");
c.bTerminated = true;
}
catch (Exception E)
{
Utility.SendEventLogError("Exception raised in Stop, " + E.Message);
}
}
class CollateralClass
{
public bool bTerminated = false;
EventLog eventLog = new EventLog();
Hashtable htReported = new Hashtable();
public void Worker()
{
int iLongerJobCounter = 361;
try
{
while (!bTerminated)
{
Thread.Sleep(1000 * 5);
iLongerJobCounter++;
//runs every five minutes
if (iLongerJobCounter > 60)
{
iLongerJobCounter = 0;
RunDBBackupJob();
}
}
}
catch (Exception E)
{
Utility.SendEventLogError("Exception raised in HousekeepingsServer (thread), " + E.Message);
}
}
}
private static void RunDBBackupJob()
{
try
{
DateTime dtLastTime = new DateTime(Utility.GetLastBackup());
bool bBackup = (DateTime.Now > dtLastTime.AddMinutes(Utility.GetBackupEvery()));
if (bBackup)
{
string sFileName = Utility.GetBackupPrefix()+ DateTime.Now.ToString("yyyyMMdd_HHmm");
string sPathName = Utility.GetBackupLocation();
Utility.SendEventLogInfo("GPHousekeepingServer:RunDBBackUpJob to "+sPathName+sFileName);
var proc = new Process
{
StartInfo =
new ProcessStartInfo
{
FileName = Utility.GetBackupBatchFile(),
Arguments = sPathName+sFileName,
UseShellExecute = false,
CreateNoWindow = false
}
};
proc.Start();
proc.WaitForExit();
int successExitCode = 0;
if (proc.ExitCode == successExitCode)
{
Utility.SendEventLogInfo("GPHousekeepingServer:RunDBBackUpJob done with exp, setting up drive");
var certificate = new X509Certificate2(Utility.GetBackupP12File(), "notasecret", X509KeyStorageFlags.Exportable);
var serviceAccountEmail = Utility.GetBackupServiceAccount();
var userAccountEmail = Utility.GetBackupUserAccount();
ServiceAccountCredential credential = new ServiceAccountCredential(
new ServiceAccountCredential.Initializer(serviceAccountEmail)
{
Scopes = new[] { Google.Apis.Drive.v3.DriveService.Scope.Drive },
User = userAccountEmail
}.FromCertificate(certificate));
// Create the service.
using (DriveService driveService = new DriveService(new BaseClientService.Initializer()
{
HttpClientInitializer = credential,
ApplicationName = Utility.GetApplicationName()
}))
{
var about = driveService.About.Get();
about.Fields = "storageQuota,user";
User user = about.Execute().User;
StringBuilder sb = new StringBuilder("<!DOCTYPE><html><head><title></title></head><body>");
sb.Append("User " + user.DisplayName + ", " + user.EmailAddress + "<br/>");
if (!System.IO.File.Exists(sPathName + sFileName + ".dmp"))
throw new Exception("Unable to location: " + sPathName + sFileName + ".dmp, did " + Utility.GetBackupBatchFile() + " run?");
FileInfo fi = new FileInfo(sPathName + sFileName + ".dmp");
//upload to drive.google
var storageQuota = about.Execute().StorageQuota;
if (storageQuota.Limit == null)
sb.Append("StorageUsage: " + Math.Round((double)(storageQuota.Usage / Math.Pow(1024, 2)), 1).ToString() + "mb, StorageLimit: Unlimited<br/>");
else
{
sb.Append("StorageUsage: " + Math.Round((double)(storageQuota.Usage / Math.Pow(1024, 2)), 1).ToString() + "mb, StorageLimit: " + (storageQuota.Limit / Math.Pow(1024, 3)) + "gb, Space Available: " + Math.Round((double)((storageQuota.Limit - storageQuota.Usage) / Math.Pow(1024, 2)), 1).ToString() + "mb<br/>");
if (fi.Length > (storageQuota.Limit - storageQuota.Usage))
{
//clean up some space by removing the oldest files. need to remove fi.length
sb.Append("drive.google is full, making space by removing:<br/>");
FilesResource.ListRequest listDelete = driveService.Files.List();
// listRequest.Spaces = "appDataFolder";
listDelete.PageSize = 100;
listDelete.Fields = "nextPageToken, files(id, name, size, createdTime)";
listDelete.OrderBy = "createdTime asc";
IList<Google.Apis.Drive.v3.Data.File> files = listDelete.Execute().Files;
long fucking=(long)storageQuota.Limit;
long stupid = (long)storageQuota.Usage;
long lBytesDeleted = fucking-stupid;
if (files != null)
{
foreach (var file in files)
{
if (file.Name.ToUpper().Contains("BBB_"))
{
sb.Append("Deleted: " + ((DateTime)file.CreatedTime).ToString("MM/dd/yyyy HHmm") + " " + file.Name + " " + file.Id + " size: " + file.Size + "<br/>");
driveService.Files.Delete(file.Id).Execute();
lBytesDeleted += (long)file.Size;
if (lBytesDeleted > fi.Length)
break;
}
}
}
}
}
//upload file
Utility.SendEventLogInfo("GPHousekeepingServer:RunDBBackUpJob " + sFileName + " uploading to drive");
Google.Apis.Drive.v3.Data.File fileMetaData = new Google.Apis.Drive.v3.Data.File();
fileMetaData.Name = sFileName + ".dmp";
using (FileStream uploadStream = new FileStream(sPathName + sFileName + ".dmp", FileMode.Open, FileAccess.Read))
{
FilesResource.CreateMediaUpload request = driveService.Files.Create(fileMetaData, uploadStream, "application/octet-stream");
request.Fields = "id,size";
request.Upload();
uploadStream.Close();
uploadStream.Dispose();
Utility.SendEventLogInfo("GPHousekeepingServer:RunDBBackUpJob File Uploaded to drive, ID: " + request.ResponseBody.Id + ", size: " + request.ResponseBody.Size + "<br/>");
sb.Append("File Uploaded, ID: " + request.ResponseBody.Id + ", size: " + request.ResponseBody.Size + "<br/>");
}
{
FilesResource.ListRequest listRequest = driveService.Files.List();
// listRequest.Spaces = "appDataFolder";
listRequest.PageSize = 100;
listRequest.Fields = "nextPageToken, files(id, name, size, createdTime)";
listRequest.OrderBy = "createdTime desc";
IList<Google.Apis.Drive.v3.Data.File> files = listRequest.Execute().Files;
if (files != null)
{
sb.Append("FILES on drive.google.com<br/>");
foreach (var file in files)
sb.Append(((DateTime)file.CreatedTime).ToString("MM/dd/yyyy HHmm") + " " + file.Name + " " + file.Id + " size: " + file.Size + "<br/>");
}
}
Utility.SetLastBackup(DateTime.Now.Ticks);
sb.Append("</body></html>");
SendEMail(new SMTPRec(sb.ToString(), "dbBackup"));
driveService.Dispose();
}
}
else
{
throw new Exception(string.Format("EXP FAILED! Process exit code not same as successExitCode specified.!! Process ExitCode={0} and successExitCode={1}", proc.ExitCode, successExitCode));
}
}
}
catch (Exception E)
{
Utility.SendEventLogError("Fatal Exception raised in HousekeepingServer.RunDBBackupJob (not-stopping), " + E.Message);
}
}
private static void SendEMail(SMTPRec smtpInfo)
{
try
{
string sBody = smtpInfo.rawHTML;
MailAddress maFrom = new MailAddress(smtpInfo.from, "Housekeeping", Encoding.UTF8);
MailAddress maTo = new MailAddress(smtpInfo.to, smtpInfo.to, Encoding.UTF8);
MailMessage mailMessage = new MailMessage(maFrom, maTo);
if (!smtpInfo.bcc.Equals(string.Empty))
mailMessage.Bcc.Add(smtpInfo.bcc);
mailMessage.Body = sBody;
mailMessage.BodyEncoding = Encoding.UTF8;
mailMessage.IsBodyHtml = true;
mailMessage.Subject = smtpInfo.subject;
mailMessage.SubjectEncoding = Encoding.UTF8;
SmtpClient smtpClient = new SmtpClient(smtpInfo.host, smtpInfo.hostPort);
smtpClient.DeliveryMethod = SmtpDeliveryMethod.Network;
smtpClient.Timeout = 20000;
if (smtpInfo.useSSL)
{
Utility.SendEventLogInfo("Using SSL");
smtpClient.EnableSsl = true;
}
if (!string.IsNullOrEmpty(smtpInfo.hostUserName))
{
Utility.SendEventLogInfo("Setting credentials smtpClient, " + smtpInfo.hostUserName + " password " + smtpInfo.hostPassword);
smtpClient.UseDefaultCredentials = false;
smtpClient.Credentials = new NetworkCredential(smtpInfo.hostUserName, smtpInfo.hostPassword); ;
}
smtpClient.Send(mailMessage);
}
catch (Exception E)
{
Utility.SendEventLogError("GPHousekeeping:SendEMail Exception Raised: "+ E.Message);
}
}
}
}
Utility.cs
using System;
using System.Data;
using System.Configuration;
using System.Text.RegularExpressions;
using System.IO;
using System.IO.Ports;
using System.Net;
using System.Net.Sockets;
using Oracle.DataAccess.Client;
using System.Diagnostics;
/// <summary>
/// Summary description for Utility
/// </summary>
public static class Utility
{
public static System.Diagnostics.EventLog myEventLog;
static Utility()
{
if (!EventLog.SourceExists("GPHousekeepingServer"))
EventLog.CreateEventSource("GPHousekeepingServer", "Application");
myEventLog = new EventLog("Application");
myEventLog.Source = "GPHousekeepingServer";
}
public static void SendEventLogError(string errorMessage)
{
try
{
if (myEventLog != null)
myEventLog.WriteEntry(errorMessage, EventLogEntryType.Error);
}
catch (Exception)
{
//nothing to do with this... :(
}
}
public static void SendEventLogInfo(string infoMessage)
{
try
{
if (myEventLog != null)
myEventLog.WriteEntry(infoMessage, EventLogEntryType.Information);
}
catch (Exception)
{
//nothing to do with this... :(
}
}
public static bool isEmail(string inputEmail)
{
string strRegex = @"^([a-zA-Z0-9_\-\.]+)@((\[[0-9]{1,3}" +
@"\.[0-9]{1,3}\.[0-9]{1,3}\.)|(([a-zA-Z0-9\-]+\" +
@".)+))([a-zA-Z]{2,4}|[0-9]{1,3})(\]?)$";
Regex re = new Regex(strRegex);
if (re.IsMatch(inputEmail))
return (true);
else
return (false);
}
public static uint IPDottedToUint(string dottedIP)
{
uint uIP = 0;
try
{
string[] sNumbers = dottedIP.Split('.');
if (sNumbers.GetLength(0) != 4)
throw new Exception("invalid format, " + dottedIP);
uIP = (uint.Parse(sNumbers[0]) * 16777216) + (uint.Parse(sNumbers[1]) * 65536) + (uint.Parse(sNumbers[2]) * 256) + (uint.Parse(sNumbers[3]));
}
catch (Exception E)
{
throw new Exception("Exception raised in IPDottedToUint, " + E.Message);
}
return uIP;
}
public static long GetLastBackup()
{
long lResult=0;
try
{
Configuration config = ConfigurationManager.OpenExeConfiguration(System.Reflection.Assembly.GetExecutingAssembly().Location);
if (config.AppSettings.Settings["lastBackupRun"] == null)
{
//to force initial run, arbitrarily subtract one day
lResult = DateTime.Now.AddDays(-1).Ticks;
}
else
{
if (!long.TryParse(config.AppSettings.Settings["lastBackupRun"].Value, out lResult))
throw new Exception("Trouble parsing lastBackupRun (ticks) from config, " + config.AppSettings.Settings["lastBackupRun"].Value);
}
}
catch (Exception E)
{
throw new Exception("Exception raised in GetLastBackup, " + E.Message);
}
return lResult;
}
public static void SetLastBackup(long ticks)
{
try
{
Configuration config = ConfigurationManager.OpenExeConfiguration(System.Reflection.Assembly.GetExecutingAssembly().Location);
if (config.AppSettings.Settings["lastBackupRun"] == null)
config.AppSettings.Settings.Add("lastBackupRun", ticks.ToString());
else
config.AppSettings.Settings["lastBackupRun"].Value = ticks.ToString();
config.Save(ConfigurationSaveMode.Minimal, false);
}
catch (Exception E)
{
throw new Exception("Exception raised in SetLastBackup, " + E.Message);
}
}
public static string GetBackupLocation()
{
string sResult = string.Empty;
try
{
Configuration config = ConfigurationManager.OpenExeConfiguration(System.Reflection.Assembly.GetExecutingAssembly().Location);
if (config.AppSettings.Settings["backuplocation"] == null)
throw new Exception("backuplocation is not set in App.config");
sResult = config.AppSettings.Settings["backuplocation"].Value;
}
catch (Exception E)
{
throw new Exception("Exception raised in GetBackupLocation, " + E.Message);
}
return sResult;
}
public static string GetBackupServiceAccount()
{
string sResult = string.Empty;
try
{
Configuration config = ConfigurationManager.OpenExeConfiguration(System.Reflection.Assembly.GetExecutingAssembly().Location);
if (config.AppSettings.Settings["backupserviceAccount"] == null)
throw new Exception("backupserviceAccount is not set in App.config");
sResult = config.AppSettings.Settings["backupserviceaccount"].Value;
}
catch (Exception E)
{
throw new Exception("Exception raised in GetBackupServiceAccount, " + E.Message);
}
return sResult;
}
public static string GetBackupUserAccount()
{
string sResult = string.Empty;
try
{
Configuration config = ConfigurationManager.OpenExeConfiguration(System.Reflection.Assembly.GetExecutingAssembly().Location);
if (config.AppSettings.Settings["backupuseraccount"] == null)
throw new Exception("backupuseraccount is not set in App.config");
sResult = config.AppSettings.Settings["backupuseraccount"].Value;
}
catch (Exception E)
{
throw new Exception("Exception raised in GetBackupUserAccount, " + E.Message);
}
return sResult;
}
public static string GetBackupPrefix()
{
string sResult = string.Empty;
try
{
Configuration config = ConfigurationManager.OpenExeConfiguration(System.Reflection.Assembly.GetExecutingAssembly().Location);
if (config.AppSettings.Settings["backupprefix"] == null)
throw new Exception("backupprefix is not set in App.config");
sResult = config.AppSettings.Settings["backupprefix"].Value;
}
catch (Exception E)
{
throw new Exception("Exception raised in GetBackupPrefix, " + E.Message);
}
return sResult;
}
public static string GetApplicationName()
{
string sResult = string.Empty;
try
{
Configuration config = ConfigurationManager.OpenExeConfiguration(System.Reflection.Assembly.GetExecutingAssembly().Location);
if (config.AppSettings.Settings["backupapplicationname"] == null)
throw new Exception("backupapplicationname is not set in App.config");
sResult = config.AppSettings.Settings["backupapplicationname"].Value;
}
catch (Exception E)
{
throw new Exception("Exception raised in GetApplicationName, " + E.Message);
}
return sResult;
}
public static string GetBackupBatchFile()
{
string sResult = string.Empty;
try
{
Configuration config = ConfigurationManager.OpenExeConfiguration(System.Reflection.Assembly.GetExecutingAssembly().Location);
if (config.AppSettings.Settings["backupbatch"] == null)
throw new Exception("backupbatch is not set in App.config");
sResult = config.AppSettings.Settings["backupbatch"].Value;
if (!File.Exists(sResult))
throw new Exception("unable to locate file, " + sResult);
}
catch (Exception E)
{
throw new Exception("Exception raised in GetBackupBatchFile, " + E.Message);
}
return sResult;
}
public static string GetBackupP12File()
{
string sResult = string.Empty;
try
{
//determine the code for the current provider
Configuration config = ConfigurationManager.OpenExeConfiguration(System.Reflection.Assembly.GetExecutingAssembly().Location);
if (config.AppSettings.Settings["backupp12"] == null)
throw new Exception("backupp12 is not set in App.config");
sResult = config.AppSettings.Settings["backupp12"].Value;
if (!File.Exists(sResult))
throw new Exception("unable to locate file, " + sResult);
}
catch (Exception E)
{
throw new Exception("Exception raised in GetBackupP12File, " + E.Message);
}
return sResult;
}
public static int GetBackupEvery()
{
int iResult = 0;
try
{
//determine the code for the current provider
Configuration config = ConfigurationManager.OpenExeConfiguration(System.Reflection.Assembly.GetExecutingAssembly().Location);
if (config.AppSettings.Settings["backupevery"] == null)
throw new Exception("backupevery is not set in App.config");
if (!int.TryParse(config.AppSettings.Settings["backupevery"].Value, out iResult))
throw new Exception("unable to convert to int, " + config.AppSettings.Settings["backupevery"].Value);
}
catch (Exception E)
{
throw new Exception("Exception raised in GetBackupEvery, " + E.Message);
}
return iResult;
}
}
Below is the assemblyname.config file
<configuration>
<appSettings>
<add key="SmtpHost" value="smtp.gmail.com" />
<add key="SmtpPort" value="587" />
<add key="SmtpHostUserName" value="" />
<add key="SmtpHostPassword" value="" />
<add key="SmtpUseSSL" value="1"/>
<add key="FromEmail" value="" />
<add key="ToEmail" value="" />
<add key="subject" value="DB Backup"/>
<add key="backuplocation" value="c:\garbage\"/>
<add key="backupbatch" value="c:\GPUtilities\cmdbackup.bat"/>
<add key="backupevery" value="480"/>
<add key="backupp12" value="c:\GPUtilities\ServerBackups.p12"/>
<add key="backupserviceaccount" value="...@...gserviceaccount.com"/>
<add key="backupuseraccount" value=""/>
<add key="backupapplicationname" value=""/>
<add key="backupprefix" value="BBB_"/>
</appSettings>
</configuration>
Example of batch file called in backupbatch for Oracle, this could be anything so long as it ultimately creates a single file %1 passed in from the service.
@ECHO OFF
SET filename=%1.dmp
SET logname=%1.log
EXP credentials FILE=%filename% OWNER=schema LOG=%logname% STATISTICS=NONE
