Backup Microsoft SQL Database and Upload to FTP

funklet

Rate me:

4.43/5 (14 votes)

3 Oct 2008CPOL2 min read

79.1K

3.2K

Make backups of SQL databases and upload them to FTP server

Download source code - 8.61 KB

Introduction

This small C# program will backup the SQL database you specify and then upload it to the FTP server. It will also delete the backups that are a specified number of days old except on the specified day.

E.g. Keep Sunday, so that you are left with daily / weekly backups.

You will have a daily backup for x days (e.g. 14) and a weekly backup that you can keep indefinitely. The backups are named DatabaseName_full_YYYYMMDD.bak.

The program can be altered easily to change these parameters.

The problem is how to back up an SQL database and then send it via FTP to a remote server. This is to avoid having to take backups off-site using CD / backup drive etc.

Background

There are tools to buy and there are scripts that use shell to FTP but I couldn't find a .NET answer to the problem.

I did find a T-SQL script and this was the basis for a re-write in C# using System.Net namespace.

Microsoft Backup and the Backup from within SQL are powerful tools, but they do not FTP.

Using the Code

All the code required is in the *.zip file. You need to specify the FTP details:

const string ftpServerURI = "ftpserver.com"; // FTP server
const string ftpUserID = "username"; // FTP Username
const string ftpPassword = "password"; //FTP Password
const string strCon = "Data Source=ServerInstance;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 = "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 = 31; 

// Number of days to keep the daily backups for.
const DayOfWeek DayOfWeekToKeep = DayOfWeek.Sunday;

// Specify which daily backup to keep indefinitely.

If you are unsure of how this works, then cut and paste the SQL into Management Studio so you can see exactly what the query will return.

You also need to specify the database that you do not wish to backup.

SqlCommand comSQL = new SqlCommand("select name 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.

Points of Interest

Please see this article on MSDN. The new .NET classes for FTP I find are poorly documented and much debugging was required to utilise the examples from MSDN.

Even if you do not require a backup program, I hope you can make some use of the FTP functions I have written.

private static bool FTPDeleteFile(Uri serverUri, NetworkCredential Cred)

If it does not exist, then the error is trapped gracefully. If some other error occurs, then this will be entered in the logfile.

Despite much searching, I could not find an example of checking if a file exists before trying to delete it. So this function will try and delete the file anyway even if it does not exist.

private static bool FTPMakeDir(Uri serverUri, NetworkCredential Cred)

You cannot upload to a directory that does not already exist on the FTP server, this function will create it.

It recursively works through the subdirectories of the URI and creates each one in turn.

If the subDir already exists, then this is trapped gracefully. If some other error occurs, then this will be entered in the logfile.

private static bool FTPUploadFile
    (String serverPath, String serverFile, FileInfo LocalFile, NetworkCredential Cred)

This will upload the file to the FTP Server. It uses FTPMakeDir to make the directory if it does not already exist.

This code is currently being used to backup around 10 databases in a SQL Server 2005 instance on a 1and1 server which has a local FTP backup server.

The database backups range from a few KB to 200 MB.

History

4^th October, 2008: Initial post

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Written By

funklet

United Kingdom

This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

Dave,

Many thanks for your feedback.
The error is almost certainly a timeout from the recieving FTP server. You need to up the timeout setting on the FTP server.
However, I question why your databases are so big.

You could try :

use master
go
select * from master.sys.sysaltfiles

This will give you the file sizes of all the databases on your SQL server.
What you might find is that the log files are of a considerable size and need to be truncated.

Also the project did not include any compression.

Below is a slight rewrite which truncates the log files and zips the files before sending them.
There is also a correction to the daystokeep although it still doesn't actually keep the correct days backup unless the daystokeep is a multiple of 7.

I hope it is of use to you.

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;
            }
        }
    }
}

Kind regards,

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.