Goran Bibic

Goran Bibic

  • 447
  • 2.9k
  • 172.7k

Create Backup and upload to FTP

Dec 7 2023 12:17 PM
class Program
{
    const string ftpServerURI = "mywebsite"; // FTP server
    const string ftpUserID = "admin"; // FTP Username
    const string ftpPassword = "admin"; //FTP Password
    const string strCon = "Data Source=.\\SQLEXPRESS;Initial Catalog=master;Persist Security Info=True;Integrated Security=True;MultipleActiveResultSets=True;"; // Change SQLDBSERVER to the name of the SQL Server you are using
    const string drive = "C"; // The local drive to save the backups to 
    const string LogFile = "C:\\Backup\\Logs\\SQLBackup.log"; // The location on the local Drive of the log files.
    const int DaysToKeep = 31; // Number of days to keep the daily backups for
    const DayOfWeek DayOfWeekToKeep = DayOfWeek.Sunday; // Specify which daily backup to keep

    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 from sysdatabases where name not in('tempdb','model','Northwind','AdventureWorks','master', 'msdb') 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);
            DriveInfo d = new DriveInfo("C");
           // FileInfo oldfn;
            //if (DateTime.Now.DayOfWeek != DayOfWeekToKeep)
            //{
            //    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(14)).ToString("yyyyMMddHHmm") + ".Bak");
            //   // FTPDeleteFile(new Uri("ftp://" + ftpServerURI + "/" + (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("yyyyMMddHHmm") + ".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);
            FTPDeleteFile(new Uri("ftp://" + ftpServerURI + "/OFFICE/" + (string)dr["name"] + "/" + fn.Name), new NetworkCredential(ftpUserID, ftpPassword));
            if (FTPUploadFile("ftp://" + ftpServerURI + "/OFFICE/" + (string)dr["name"], "/" + fn.Name, fn, new NetworkCredential(ftpUserID, ftpPassword)))
            {
                WriteLog("Upload Succeeded", fnLog);
            }
            else
            {
                WriteLog("Upload Failed", fnLog);
            }
            comSQL2.Connection.Close();
        }
        comSQL.Connection.Close();
    }
}

FROM MY PC WORK, ANOTHER PC FROM SAME SETUP SQL SERVER NOT WORK

Some help?


Answers (3)