Securely backup your SQL Server database and other files to a remote machine

• Download source files - 288.5 KB

Introduction

What a hassle it is to do backups in SQL Server! You have to open Enterprise Manager, scroll down to the database you want, right click, select "Backup" etc...boring!

This application will periodically:

• Backup the database of choice
• Zip the database backup (very compressible!) along with any other files you want
• Encrypt that zip
• Send the file to a remote machine

You may be wondering, "Why didn't you just use a maintenance plan?" - Good question. Although a maintenance plan would provide more SQL server backup options (incremental, full) it does NOT support compression or encryption. This program also has an option to backup a set of user-defined files, in addition or instead of the SQL server backup.

Using the code

Let's take a look at the application config file first.

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<appSettings>
    <add key="BackupName" value="NorthwindBackup"/>
                  <!-- The base name of the backup file -->
    <add key="DBName" value="Northwind"/>
                  <!-- The database to be backed up -->
    <add key="DBUsername" value="sa"/>
                  <!-- The user to login to database -->
    <add key="DBPassword" value=""/>
                  <!-- The password of the database user -->

    <add key="BackupDirs" value="C:\app_server\"/>
                  <!-- Any files or directories to be backed up -->
    <add key="ExcludeDirs" value="bin,obj"/>
                  <!-- The name of directories to EXCLUDE when backing up -->
    <add key="SSHServer" value="offsite.com"/>
                  <!-- Remote server and login details -->
    <add key="SSHUsername" value="username"/>
    <add key="SSHPassword" value="password"/>

    <add key="KeyString" value="f+WAB8/Spgu3w6LLFM10a2ZyRWIy8vaVTvF/lhLSaME="/>
                  <!-- Key used to encrypt backup -->
    <add key="IVString" value="B3vXRDLkYltYjl3QzZP2jQ=="/>
                  <!-- IV used to encrypt backup -->
    <add key="IsBackup" value="true"/>
                  <!-- Whether you are backing up or restoring -->
</appSettings>
</configuration>

The first setting, BackupName, is simply the name of the zip file where we put the database backup and any files to be backed up. In the above example, this zip file would contain the Northwind database and any files under 'C:\app_server\'. The zip is then encrypted and is sent using SSH to the machine specified by SSHServer, or in this case, offsite.com

A few things to note:

• If you just want to backup files (NOT SQL Server) then set DBName="" (likewise clear out BackupDirs to only perform SQL Backup)
• All backups are date-stamped to prevent overwriting - in the above example the remote file is named NorthwindBackup_20070411.encrypted
• The keys used to encrypt the data are stored in the config file and thus you must encrypt the config file - see below for how to do this.

Probably the most interesting part is how to backup the database from C#. The following T-SQL does just that:

DECLARE @Date VARCHAR(300), @Dir VARCHAR(4000)

--Get today's date
SET @Date = CONVERT(VARCHAR, GETDATE(), 112)

--Set the directory where the back up file is stored
SET @Dir = 'E:\BackupUtil\NorthwindBackup_20070411.bak'

--create a 'device' to write to first
EXEC sp_addumpdevice 'disk', 'temp_device', @Dir

--now do the backup
BACKUP DATABASE Northwind TO temp_device WITH FORMAT

--Drop the device
EXEC sp_dropdevice 'temp_device'

Please note that this is dynamically generated code. Obviously the name of the database changes as does the name of the backup file.

Making use of existing code

I used external libraries to zip the backup and also to securely send it.

• Compression - ICSharpCode.SharpZipLib
• SSH - sharpSsh

How about running the backup periodically? No worries there, just use Scheduled Tasks in Windows (Start -> Control Panel -> Scheduled Tasks) Use the wizard to point to the backup executable. When selecting how often to perform the task, I recommend selecting "Weekly".

Now you can select individual days (e.g. weekdays only)

Had you selected "Daily", you would not have the choice to run it on weekdays only.

Security

I strongly suggest you create a user on the remote machine purely for the backups. Thus if the account gets compromised, the user only has access to the encrypted backups (and not the entire machine!). Strictly speaking the transfer to the remote machine could have been done unsecurely, (eg FTP) but I like SSH...

Last but not the least, I know some will be outraged by storing the encryption keys, remote machine login details and database password in "plaintext". You *must* encrypt the config file by right clicking and selecting Properties:

Now click on Advanced (circled above)

Ensure that "Encrypt contents to secure data" is checked.

The config file can now only be read by the user who applied the above.

Restoring

Firstly, you need to retrieve your backup file from the remote machine (the .encrypted file) Then in the config file the following line needs to be changed from

<add key="IsBackup" value="true"/>
            <!-- Whether you are backing up or restoring -->

to

<add key="IsBackup" value="false"/>
            <!-- Whether you are backing up or restoring -->

When you run the application with IsBackup=false, a file browser will appear asking you which .encrypted file you wish to restore. This .encrypted file turns into a .zip file which contains your database and backed up files - Too easy!

Finally...

Comments are much appreciated, I always want to make it "more better".