15 people following this project (follow)

Project Description
This project provides a free client class library on any platform supporting the .Net Framework 4.0 to backup Microsoft SQL 2000/2005/2008 & 2008 R2 databases and script tables, stored procedures, views, and user defined functions using SQL Server Management Objects (SMO). The application supports 7-zip compression of the database to backup files with Aes256 encryption. The application also supports using FTP, FTPS and SFTP to send the database files to multiple remote servers. Also added support for sending SMTP emails after backup is complete.

Backup, FTP & Email Example
Example1.png

Script Database Object Example
Example3.png

Script Table Data Example
Example4.png

SQL Logging Example
Example2.png

SQLBackupFTP version 1.0

Copyright (C) Matthew David Elgert 2012 - mdelgert@yahoo.com

ABOUT

SQLBackupFTP is a MSSQL backup and FTP program.

REQUIREMENTS

Microsoft .Net Framework 4.0 http://www.microsoft.com/download/en/details.aspx?id=17718

FEATURES

  • Microsoft SQL Server 2000/2005/2008R2 Database Backup
  • Script Database objects for DB version control
  • Schedule Backup jobs
  • Send backups to FTP, SFTP, FTPS, local or network folder
  • Send e-mail log files
  • AES 256 encryption 7-Zip files

USAGE

1. Modify SQLBackupFTP.exe.Config file

Sample SQLBackupFTP.exe.Config

<?xml version="1.0"?>
<configuration>
  <connectionStrings>
    <add name="ApplicationServices" connectionString="data source=localhost;UID=sa;PWD=Password;initial catalog=Master" providerName="System.Data.SqlClient" />
  </connectionStrings>
  <system.net>
    <mailSettings>
      <smtp deliveryMethod="Network" from="info@yourdomainname.com">
        <network host="smtp.gmail.com" port="587" userName="info@yourdomainname.com" password="yourpassword" enableSsl="true" />
      </smtp>
    </mailSettings>
  </system.net>
  <appSettings>
    <add key="TimeStamp" value="20120221101302" />
    <add key="SQLCommandTimeout" value="999999999" />
    <add key="LogDB" value="SQLBackupFTP" />
    <!--Gmail server has a bug that does not allow send to address from the same address in this case send through an open mail relay-->
    <add key="SMTPRelayPort" value="25" />
    <add key="SMTPRelayEnableSsl" value="false" />
    <add key="SMTPRelayHost" value="s2smtpout.secureserver.net" />
    <add key="SendEmailTo" value="info@yourdomainname.com"/>
    <add key="SendEmailFrom" value="info@yourdomainname.com"/>
    <add key="SendEmailSubject" value="SQLBackupFTPLog home database"/>
    <!--Add password to file if blank no password-->
    <add key="ArchivePwd" value="Nf3PF9mTh$VGNZ8TW%^S%3HTWf$^S2zz#rPMxYs5" />
    <!--Default Application root\SQLBackupFTPTemp\-->
    <!--<add key="TempDir" value="D:\SQLBackupFTPTemp\" />-->
    <!--Default Log.txt-->
    <add key="LogFile" value="Log.txt"/>
    <!--Default 120000 //ms-->
    <add key="FTPTimeOut" value="120000"/>
    <!--Default false-->
    <add key="BackupAll" value="False"/>
    <add key="ConsoleExit" value="False"/>
    <!--Deletes the entire temp folder with 7 zip files and logs-->
    <add key="DelTemp" value="False"/>
    <!--Deletes just the temp files to create the 7 zip files keep the logs-->
    <add key="DelBakTemp" value="False" />
    <add key="ScriptDB" value="True" />
    <add key="ScriptTableData" value="True" />
    <add key="BackupDBOff" value="False"/>
    <add key="FTPOff" value="False"/>
    <add key="SevenZipOff" value="False"/>
    <add key="SendMailOff" value="False"/>
  </appSettings>
</configuration>

2. Modify and add ftp servers to FTPServers.xml file

Sample FTPServers.xml

<?xml version="1.0" encoding="utf-8" ?>
<XMLDocument>
  <Server>
    <Host>ftp.yourdomain1.com</Host>
    <User>username</User>
    <Pass>password</Pass>
    <RemoteFolder>/DBBackups/</RemoteFolder>
    <SSL>True</SSL>
    <SFTP>False</SFTP>
  </Server>
  <Server>
    <Host>ftp.yourdomain2.com</Host>
    <User>username</User>
    <Pass>password</Pass>
    <RemoteFolder>/array1/data/Backups/DBBackups/</RemoteFolder>
    <SSL>False</SSL>
    <SFTP>False</SFTP>
  </Server>
  <Server>
    <Host>ftp.yourdomain3.com</Host>
    <User>username</User>
    <Pass>password</Pass>
    <RemoteFolder>/DBBackups/</RemoteFolder>
    <SSL>False</SSL>
    <SFTP>True</SFTP>
  </Server>
</XMLDocument>

3. Modify and add databases to Databases.xml file

Sample Databases.xml

<?xml version="1.0" encoding="utf-8" ?>
<XMLDocument>
  <Database>
    <DBName>master</DBName>
  </Database>
  <Database>
    <DBName>model</DBName>
  </Database>
  <Database>
    <DBName>msdb</DBName>
  </Database>
  <Database>
    <DBName>Northwind</DBName>
  </Database>
  <Database>
    <DBName>pubs</DBName>
  </Database>
</XMLDocument>

PROJECT REFERENCES

SevenZipSharp SevenZip.dll http://sevenzipsharp.codeplex.com/ 7z.dll http://www.7-zip.org/sdk.html

Alex FTPS Client FTPS.Client.dll http://ftps.codeplex.com/

SharpSSH2 Tamir.SharpSSH.dll http://sharpssh2.codeplex.com/ DiffieHellman.dll http://www.mentalis.org/soft/class.qpx?id=15 Org.Mentalis.Security.dll http://www.mentalis.org/soft/projects/seclib/download.qpx

Task Scheduler Managed Wrapper Microsoft.Win32.TaskScheduler.dll http://taskscheduler.codeplex.com/

Microsoft SQL Server 2008 R2 Express Microsoft.SqlServer.ConnectionInfo.dll, Microsoft.SqlServer.Management.Sdk.Sfc.dll, Microsoft.SqlServer.Smo.dll http://www.microsoft.com/betaexperience/pd/SQLEXP08V2/enus/

CHANGELOG

2/23/2012 Version 1.1
* Added Task Scheduler

2/21/2012 Version 1.0
* First release

Last edited Feb 23 at 6:59 AM by mdelgert, version 20