Automated cloud backups of MS SQL Server DB

[novice32]

Hey Everyone.. You'll sleep well at home if you need frequent MS SERVER backups to say, your Amazon S3 account. The script uses two free utilities, S3 copy and 7za (free zip utility from 7-zip). Create the below directories referenced in the script.

The code isn't perfect, but you'll get the gist. I'm no where near a windows command line expert, so it probably could be optimized. This works on my Windows 2003 VPS account.

One more thing, use S3 to confirm access permissions to your backup and bucket items.

Please provide feedback.

Cheers!!!

--------------------------------------------------------------
:: I called my cmd file DB-BACKUP.cmd and scheduled with Windows scheduler.
:: execute command to perform full bakcup of database

@echo on

::run script to backup database; this will create a file, c:\SQLBACKUP\DATAFILES\TestDB.
sqlcmd -i C:\SQLBACKUP\backup.sql
::backup.sql would have some similar to the below
::BACKUP DATABASE [TestDB] TO DISK = N'C:\SQLBACKUP\DATAFILES\TestDB.bak' WITH NOFORMAT, INIT, NAME = N'TestDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
::GO

::create variables for new file names
::%date:~4,2% => 2-digit month, %date:~7,2% =>2-digit day, %date:~10,4% => 4 digit year. Sample output, 02-28-2010
set date_string=%date:~4,2%-%date:~7,2%-%date:~10,4%

::%TIME:~0,2% => 2-char hour, %TIME:~3,2% => 2 digit minute, %TIME:~6,2% => 2 digit second.
set time_string=%TIME:~0,2%.%TIME:~3,2%.%TIME:~6,2%

::concatenate string
set DateTimeName=%date_string%_%time_string%

set FileName=TestDB_%DateTimeName%.bak

::remove blank spaces
set FileName=%FileName: =%

set ZipFileName=TestDB_%DateTimeName%.zip

::remove blank spaces
set ZipFileName=%ZipFileName: =%

set BackupDirectory=DATAFILES

::now rename the name of the backed up database file to new name with date and timestamp
ren C:\SQLBACKUP\DATAFILES\TestDB.bak %FileName%

::use 7-zip program to zip the file
c:\SQLBACKUP\7za.exe a -tzip %BackupDirectory%\%ZipFileName% %BackupDirectory%\%FileName%

::now use s3copy to transfer zip file to amazon s3 account
::you can use the S3Fox Firefox plugin to connect to S3 and find you bucket name and eventually view backup files.
c:\SQLBACKUP\s3copy.exe c:\SQLBACKUP\DATAFILES\ <amazon_bucket_name without "/" at end> %ZipFileName% <s3 access id key> <s3 secret key>

::delete the backup file (.BAK) - we'll just keep the zip version
del %BackupDirectory%\%FileName%

::search thru DATAFILES directory and delete zip files older than say 3 days
::see

http://ss64.com/nt/forfiles.html

for command details
forfiles -p C:\SQLBACKUP\DATAFILES\ -s -m *.* -d -3 -c "cmd /c del @FILE