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

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

Rate me:
Please Sign up or sign in to vote.
4.25/5 (5 votes)
15 Apr 20073 min read 75.4K   1.3K   57   15
An effective utility to securely backup an SQL Server database and/or a group of files to a remote machine

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
<?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:

SQL
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.

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".

Image 1

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

Image 2

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:

Image 3

Now click on Advanced (circled above)

Image 4

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

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

to

XML
<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".

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


Written By
Software Developer
Australia Australia
Currently working in the finance industry. Other interests: surfing, kiting, guitar, metal, golf and Opeth.

Comments and Discussions

 
SuggestionAnother good tool to make encrypted backups automatically Pin
namaste2sadhu16-Apr-14 21:40
namaste2sadhu16-Apr-14 21:40 
GeneralSqlBackupAndFTP Pin
Ruslan Sudentas8-May-09 19:29
Ruslan Sudentas8-May-09 19:29 
GeneralRe: SqlBackupAndFTP Pin
wallace turner11-Sep-11 14:30
wallace turner11-Sep-11 14:30 
GeneralI found a great tool to backup your sql using VDI Pin
roby548-Nov-08 11:00
roby548-Nov-08 11:00 
QuestionMultiple Databases Pin
N SPPC29-Nov-07 17:34
N SPPC29-Nov-07 17:34 
AnswerRe: Multiple Databases Pin
wallace turner30-Nov-07 2:31
wallace turner30-Nov-07 2:31 
GeneralThank you Pin
Fiwel4-Jul-07 7:22
Fiwel4-Jul-07 7:22 
Generalmaintenance plan Pin
dotnetangel15-Apr-07 13:33
professionaldotnetangel15-Apr-07 13:33 
GeneralRe: maintenance plan Pin
wallace turner15-Apr-07 13:58
wallace turner15-Apr-07 13:58 
AnswerRe: maintenance plan Pin
dotnetangel15-Apr-07 21:47
professionaldotnetangel15-Apr-07 21:47 
GeneralRe: maintenance plan Pin
wallace turner15-Apr-07 22:03
wallace turner15-Apr-07 22:03 
GeneralRe: maintenance plan Pin
dotnetangel15-Apr-07 22:48
professionaldotnetangel15-Apr-07 22:48 
GeneralBackup to local drive or folder Pin
HRiazi15-Apr-07 9:36
HRiazi15-Apr-07 9:36 
GeneralRe: Backup to local drive or folder Pin
wallace turner15-Apr-07 13:08
wallace turner15-Apr-07 13:08 
GeneralRe: Backup to local drive or folder Pin
HRiazi15-Apr-07 20:21
HRiazi15-Apr-07 20:21 

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.