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.
="1.0"="utf-8"
<configuration>
<appSettings>
<add key="BackupName" value="NorthwindBackup"/>
<add key="DBName" value="Northwind"/>
<add key="DBUsername" value="sa"/>
<add key="DBPassword" value=""/>
<add key="BackupDirs" value="C:\app_server\"/>
<add key="ExcludeDirs" value="bin,obj"/>
<add key="SSHServer" value="offsite.com"/>
<add key="SSHUsername" value="username"/>
<add key="SSHPassword" value="password"/>
<add key="KeyString" value="f+WAB8/Spgu3w6LLFM10a2ZyRWIy8vaVTvF/lhLSaME="/>
<add key="IVString" value="B3vXRDLkYltYjl3QzZP2jQ=="/>
<add key="IsBackup" value="true"/>
</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)
SET @Date = CONVERT(VARCHAR, GETDATE(), 112)
SET @Dir = 'E:\BackupUtil\NorthwindBackup_20070411.bak'
EXEC sp_addumpdevice 'disk', 'temp_device', @Dir
BACKUP DATABASE Northwind TO temp_device WITH FORMAT
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".
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"/>
to
<add key="IsBackup" value="false"/>
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".