Backup Microsoft SQL Database and Upload to FTP

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)

About the Author

funklet

United Kingdom

Member

No Biography provided

Article Top

Poor

Excellent

Add a reason or comment to your vote: x
Votes of 3 or less require a comment

Comments and Discussions

You must Sign In to use this message board.

Search this forum
	Profile popups Spacing Noise Layout Per page

Even though this was written 2 years back still it is valid and working perfectly. thanks. srimal_hs
Sign In·View Thread·Permalink

Simple. But Excellent. Thanks
Sign In·View Thread·Permalink

EZManage SQL Pro provids 5 time faster sql backup using VDI and ftp upload http://www.futureitsoft.com
Sign In·View Thread·Permalink

Hi I like the project, however I have two DBs of 1gb and 7gb and I am getting errors during upload . The full error is: Underlying connection closed - an unexpected error occured on a receive. I imagine it is due to the file sizes, is there anything else I can set to alleviate this problem? Thanks Dave
Sign In·View Thread·Permalink

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,

Hi Funklet Thanks for the code update - much appreciated! I will give this a go later in the week. My Db's are large due to data as opposed to log files. I cannot adjust the time out on the server unfortunately as it's not my server, I hoped it was a client setting, however if the files are zipped that's fine as they zip up quite small. I will let you know how I get on. Dave
Sign In·View Thread·Permalink

Just for your info, I was testing this on a DEV PC and calling MS-SQL on a different box. The backups were created on the SQL Server as expected, but the original code tried to copy (FTP) the backup from the local computer and not the MS-SQL box, hence the same error. Oh, and I was using VS2005 Net 2, so the zip stuff not available. Great program though, thanks and cheers!
Sign In·View Thread·Permalink

This is a very good article. I have the same problem of backing up a postgress db and sending it to an ftp site. You code got me over the initial problem. I now have a similar program working but for postgres. It works to a ftp site very well. Great work.
Sign In·View Thread·Permalink

The code is working, but such a typical task should not require programming. SQL Server (non Express) built-in tools are sufficient for many. Or something like freeware SQLBackupAndFTP[^] will do the work with no effort.
Sign In·View Thread·Permalink

This article was written as an example program for the new .NET ftp class, for which helper functions have been provided, and to help to ensure more databases are backed up. Of course there are many ways of creating and uploading backups. I thank you for your comment. Kind Regards, Funklet
Sign In·View Thread·Permalink

Downloading now! im going to check how it works. thanks < out of darkness />
Sign In·View Thread·Permalink

So interesting I'm going to play around with it. Thanks Adding manpower to a late software project only makes it later.
Sign In·View Thread·Permalink