Click here to Skip to main content
15,867,835 members
Articles / Database Development / SQL Server
Article

Backup Microsoft SQL Database and Upload to FTP

Rate me:
Please Sign up or sign in to vote.
4.43/5 (14 votes)
3 Oct 2008CPOL2 min read 78.8K   3.2K   63   14
Make backups of SQL databases and upload them to FTP server

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:

C#
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.

C#
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.

C#
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.

C#
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.

C#
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

  • 4th 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
United Kingdom 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

 
QuestionAnother great tool for SQL backups Pin
Wrangly31-Aug-21 22:52
Wrangly31-Aug-21 22:52 
SuggestionEMS SQL Backup Pin
namaste2sadhu20-Apr-14 22:48
namaste2sadhu20-Apr-14 22:48 
GeneralThanks Pin
srimal_s10-Oct-10 20:26
srimal_s10-Oct-10 20:26 
GeneralThanks Pin
picaleo6-Dec-09 16:27
picaleo6-Dec-09 16:27 
Generalnice tool that can do that as well. Pin
itayl27-May-09 3:16
itayl27-May-09 3:16 
GeneralUnderlying connection closed Pin
bigdavelamb6-Dec-08 1:34
bigdavelamb6-Dec-08 1:34 
GeneralRe: Underlying connection closed Pin
funklet6-Dec-08 22:58
funklet6-Dec-08 22:58 
GeneralRe: Underlying connection closed Pin
bigdavelamb8-Dec-08 5:30
bigdavelamb8-Dec-08 5:30 
GeneralRe: Underlying connection closed Pin
paulray27-Mar-12 9:31
paulray27-Mar-12 9:31 
GeneralPostgres Pin
Donsw10-Nov-08 13:31
Donsw10-Nov-08 13:31 
GeneralSQLBackupAndFTP Pin
Ruslan Sudentas9-Oct-08 8:45
Ruslan Sudentas9-Oct-08 8:45 
GeneralRe: SQLBackupAndFTP Pin
funklet9-Oct-08 11:25
funklet9-Oct-08 11:25 
AnswerNice Pin
outdarkman9-Oct-08 8:24
outdarkman9-Oct-08 8:24 
GeneralInteresting Approach Pin
BGaddis6-Oct-08 15:25
BGaddis6-Oct-08 15:25 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

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