Since I've started using an offsite backup service, I've been feeling better about my disaster recovery ability. However, I wanted to ensure that my files stayed small so that a) they would transfer faster to the offsite backup, and b) so they'd take up less space, which saves me money on storage.

So here's an updated SQL Command Line Backup script (see my previous post for details and the SQL script I used):

@echo off 
REM get today's date and time as one big string
for /f "tokens=2-4 delims=/ " %%i in ( 'date /t') do set theday=%%k%%i%%j
for /f "tokens=1-2 delims=: " %%i in ( 'time /t') do set thetime=%%i%%j
set now=%theday%%thetime%

REM create backup files
C:
cd "\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn"
sqlcmd -S localhost -U sa -P somepassword -i c:\sqlbackup\backup.sql -o c:\sqlbackup\log.txt

REM compress backup files
gzip -f C:\sqlbackup\*.bak

REM copy backup files to backup device
xcopy "C:\sqlbackup\*.*" \\10.1.10.50\ben_backup\sqlbackup\%now%\ /E /H /R 

The main difference is I'm now using a command line compression utility called gzip to compress the database backups (down to 10-13% of their original size). Gzip is open source, fast, and small. Obviously, you could use other utilities like WinZip's command line utility, or even Windows Services for Unix (with its ironic 230+mb download). But I figured free was good enough for me. You can download a Win32 version of gzip here.

FYI, the 10.1.10.50\ben_backup\ folder is on a local file server with RAID1, so it's relatively stable. But I now also have Mozy watching that folder, too, so everything that gets dumped there gets backed up over the internet.

Hopefully the above has given you some ideas on how to protect your code. By make regular database & source code repository dumps, and backing them up, you'll be in a better position to recover if your computer goes down, or worse.