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