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:
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:
DBName="" (likewise clear out BackupDirs to only perform SQL Backup)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.
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.
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.
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!
Comments are much appreciated, I always want to make it "more better".
| You must Sign In to use this message board. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
