Backup Microsoft SQL Database and Upload to FTP

use master
go
select * from master.sys.sysaltfiles

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.IO;
using System.Net;
using System.IO.Compression ;
using System.IO.Packaging;
 
namespace BackupDB
{
    class Program
    {
        const string ftpServerURI = "ftp.domain.com"; // FTP server
        const string ftpUserID = "username";
        const string ftpPassword = "password";
        const string strCon = "Data Source=SQLServer;Initial Catalog=master;Persist Security Info=True;Integrated Security=True;MultipleActiveResultSets=True;";
        const string drive = "D"; // The local drive to save the backups to 
        const string LogFile = "D:\\Backup\\Logs\\SQLBackup.log"; // The location on the local Drive of the log files.
        const int DaysToKeep = 30;
 
        private static string fnLog;
        static void Main(string[] args)
        {
            fnLog = RotateLog(new FileInfo(LogFile), DaysToKeep);
            WriteLog("Starting Weekly Backup.", fnLog);
            Backup();
            WriteLog("Daily Backup Finished.", fnLog);
        }
        static void Backup()
        {
            SqlCommand comSQL = new SqlCommand("select name, dbid from sysdatabases where name not in('tempdb','model','Northwind','AdventureWorks','master') order by name ASC", new SqlConnection(strCon)); // need to specify here which databases you do not want to back up.
            comSQL.Connection.Open();
            SqlDataReader dr = comSQL.ExecuteReader();
            while (dr.Read())
            {
                WriteLog("Backing Up Database - " + (string)dr["name"], fnLog);
                SqlCommand comSQL3 = new SqlCommand("select [name] from sys.master_files where [database_id] = @0 and file_id = 2 order by [name] asc", new SqlConnection(strCon));
                comSQL3.Connection.Open();
                comSQL3.Parameters.AddWithValue("@0", dr["dbid"]);
                string lfn = comSQL3.ExecuteScalar().ToString();
                comSQL3.CommandText = "USE " + dr["name"].ToString() + "; BACKUP LOG " + dr["name"].ToString() + " WITH TRUNCATE_ONLY; DBCC SHRINKFILE (" + lfn + ", 1);";
                comSQL3.CommandTimeout = 360;
                comSQL3.ExecuteNonQuery();
                comSQL3.Connection.Close();
                comSQL3.Dispose();
                DriveInfo d = new DriveInfo("D");
                FileInfo oldfn;
                if (DateTime.Now.DayOfWeek != DayOfWeek.Sunday)
                {
                    WriteLog("Deleting Backup from " + DaysToKeep.ToString() + " days ago", fnLog);
                    oldfn = new FileInfo(d.ToString() + "Backup\\" + (string)dr["name"] + "\\" + (string)dr["name"] + "_full_" + DateTime.Now.Subtract(TimeSpan.FromDays(DaysToKeep)).ToString("yyyyMMdd") + ".bak");
                    FTPDeleteFile(new Uri("ftp://" + ftpServerURI + "/SQLBackup/" + (string)dr["name"] + "/" + oldfn.Name), new NetworkCredential(ftpUserID, ftpPassword));
                }
                else
                {
                    WriteLog("Keeping Weekly Backup.", fnLog);
                }
                FileInfo fn = new FileInfo(d.ToString() + "Backup\\" + (string)dr["name"] + "\\" + (string)dr["name"] + "_full_" + DateTime.Now.ToString("yyyyMMdd") + ".bak");
                if (File.Exists(fn.FullName))
                {
                    WriteLog("Deleting Backup Because it Already Exists.", fnLog);
                    File.Delete(fn.FullName);
                }
                Directory.CreateDirectory(fn.DirectoryName);
                SqlCommand comSQL2 = new SqlCommand("BACKUP DATABASE @db TO DISK = @fn;", new SqlConnection(strCon));
                comSQL2.CommandTimeout = 360;
                comSQL2.Connection.Open();
                comSQL2.Parameters.AddWithValue("@db", (string)dr["name"]);
                comSQL2.Parameters.AddWithValue("@fn", fn.FullName);
                WriteLog("Starting Backup", fnLog);
                comSQL2.ExecuteNonQuery();
                WriteLog("Backup Succeeded.", fnLog);
                WriteLog("Uploading Backup to FTP server", fnLog);
                AddFileToZip(fn.FullName.Replace(".bak", ".zip"), fn.FullName);
                fn = new FileInfo(fn.FullName.Replace(".bak", ".zip"));
                FTPDeleteFile(new Uri("ftp://" + ftpServerURI + "/SQLBackup/" + (string)dr["name"] + "/" + fn.Name), new NetworkCredential(ftpUserID, ftpPassword));
                if (FTPUploadFile("ftp://" + ftpServerURI + "/SQLBackup/" + (string)dr["name"], "/" + fn.Name, fn, new NetworkCredential(ftpUserID, ftpPassword)))
                {
                    WriteLog("Upload Succeeded", fnLog);
                    File.Delete(fn.FullName);
                }
                else
                {
                    WriteLog("Upload Failed", fnLog);
                }
                comSQL2.Connection.Close();
                comSQL.Dispose();
            }
            comSQL.Connection.Close();
            comSQL.Dispose();
        }
        private static bool FTPDeleteFile(Uri serverUri, NetworkCredential Cred)
        {
            bool retVal = true;
            FtpWebResponse response = null;
            try
            {
                FtpWebRequest request = (FtpWebRequest)WebRequest.Create(serverUri);
                request.Method = WebRequestMethods.Ftp.DeleteFile;
                request.Credentials = Cred;
                response = (FtpWebResponse)request.GetResponse();
                response.Close();
            }
            catch (Exception ex)
            {
                if (ex.Message != "The remote server returned an error: (550) File unavailable (e.g., file not found, no access).")
                {
                    Console.WriteLine("Error in FTPDeleteFile - " + ex.Message);
                    if (response != null)
                    {
                        response.Close();
                    }
                    retVal = false;
                }
            }
            return retVal;
        }
        private static bool FTPUploadFile(String serverPath, String serverFile, FileInfo LocalFile, NetworkCredential Cred)
        {
            bool retVal = true;
            FtpWebResponse response = null;
            try
            {
                FTPMakeDir(new Uri(serverPath + "/"), Cred);
                FtpWebRequest request = (FtpWebRequest)WebRequest.Create(serverPath + serverFile);
                request.Method = WebRequestMethods.Ftp.UploadFile;
                request.Credentials = Cred;
                byte[] buffer = new byte[10240];    // Read/write 10kb   
                using (FileStream sourceStream = new FileStream(LocalFile.ToString(), FileMode.Open))
                {
                    using (Stream requestStream = request.GetRequestStream())
                    {
                        int bytesRead;
                        do
                        {
                            bytesRead = sourceStream.Read(buffer, 0, buffer.Length);
                            requestStream.Write(buffer, 0, bytesRead);
                        } while (bytesRead > 0);
                    }
                    response = (FtpWebResponse)request.GetResponse();
                    response.Close();
 
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error in FTPUploadFile - " + ex.Message);
                if (response != null)
                {
                    response.Close();
                }
                retVal = false;
            }
            return retVal;
        }
        private static bool FTPMakeDir(Uri serverUri, NetworkCredential Cred)
        {
            bool retVal = false;
            FtpWebResponse response = null;
            try
            {
                string[] ar = serverUri.ToString().Split('/');
                string makeDirUri = ar[0] + "//" + ar[2] + "/";
                for (int i = 3; i < ar.GetUpperBound(0); i++)
                {
                    makeDirUri += ar[i] + "/";
                    FtpWebRequest request = (FtpWebRequest)WebRequest.Create(new Uri(makeDirUri));
                    request.KeepAlive = true;
                    request.Method = WebRequestMethods.Ftp.MakeDirectory;
                    request.Credentials = Cred;
                    try
                    {
                        response = (FtpWebResponse)request.GetResponse();
                    }
                    catch (Exception ex)
                    {
                        if (ex.Message != "The remote server returned an error: (550) File unavailable (e.g., file not found, no access).")
                        {
                            retVal = false;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error in FTPMakeDir - " + ex.Message);
                retVal = false;
                if (response != null)
                {
                    response.Close();
                }
            }
            return retVal;
        }
        private static string RotateLog(FileInfo LogFileName, int Days)
        {
            string fNew = LogFileName.Directory.ToString() + DateTime.Now.ToString("\\\\yyyyMMdd_") + LogFileName.Name;
            string fOld = LogFileName.Directory.ToString() + DateTime.Now.Subtract(System.TimeSpan.FromDays(Days)).ToString("\\\\yyyyMMdd_") + LogFileName.Name;
            string fOldRecycler = "C:\\RECYCLER\\" + DateTime.Now.Subtract(System.TimeSpan.FromDays(Days)).ToString("yyyyMMdd_") + LogFileName.Name;
            if (File.Exists(fOld))
            {
                WriteLog("Deleting LogFile - " + fOld + " because it is over " + Days.ToString() + " Days old", "D:\\Backup\\Logs\\BackupLog");
                File.Move(fOld, fOldRecycler);
            }
            return fNew;
        }
        private static void WriteLog(string s, string fn)
        {
            File.AppendAllText(fn, DateTime.Now.ToString("dd/MM/yyyy HH:mm:ss:ffff") + " - " + s + Environment.NewLine);
        }
 
       private const long BUFFER_SIZE = 4096;
 
        private static void AddFileToZip(string zipFilename, string fileToAdd)
        {
            using (Package zip = System.IO.Packaging.Package.Open(zipFilename, FileMode.Create))
            {
                string destFilename = ".\\" + Path.GetFileName(zipFilename);
                Uri uri = PackUriHelper.CreatePartUri(new Uri(destFilename, UriKind.Relative));
                if (zip.PartExists(uri))
                {
                    zip.DeletePart(uri);
                }
                PackagePart part = zip.CreatePart(uri, string.Empty, CompressionOption.Normal);
                using (FileStream fileStream = new FileStream(fileToAdd, FileMode.Open, FileAccess.Read))
                {
                    using (Stream dest = part.GetStream())
                    {
                        CopyStream(fileStream, dest);
                    }
                }
                File.Delete(fileToAdd);
            }
        }
 
        private static void CopyStream(System.IO.FileStream inputStream, System.IO.Stream outputStream)
        {
            long bufferSize = inputStream.Length < BUFFER_SIZE ? inputStream.Length : BUFFER_SIZE;
            byte[] buffer = new byte[bufferSize];
            int bytesRead = 0;
            long bytesWritten = 0;
            while ((bytesRead = inputStream.Read(buffer, 0, buffer.Length)) != 0)
            {
                outputStream.Write(buffer, 0, bytesRead); bytesWritten += bufferSize;
            }
        }
    }
}