I currently backup my forum database on a daily basis to a network share. As the database grows consumes more and more network bandwidth (which my host charges for).
I have already customised the maintainence job to send email and post to my forum when a backup starts and finishes. I was wondering if there is a way I could use GZIP, Winzip or Winrar command line to compress the backup before copying it to the network share?
Never used xp_cmdshell, but it seems your suggestion could be an option. Likely you wouldn't even need a batch file, you could just use successive xp_cmdshell calls to compress and then transfer the file.
I came across this article - http://www.databasejournal.com/features/mssql/article.php/3064361 Its quite good but not exactly what I need. Basically you backup the database to a disk on the SQL Server and then run a batch file which compresses the backup locally using command line winzip. Part II goes on to add a feature which deletes the original backup. Its actually easier to do than suggested in the article e.g. the author could have added an argument to command line winzip which would delete the original backup automatically. To copy the compressed backup to a network share just requires another simple copy command in the batch file.
Although the above will save a lot of bandwidth, I'm missing MSSQL's feature which deletes backups older than n days. I'm talking about the compressed backups on the network share (I have to delete them every 1-2 days at the moment). Not so easy to do from a batch file when there are multiple backups from different time periods. The only thing I can think of is writing a console app in .net (which uses the FileSystemObject) instead of the batch file.
You're right BTW about being able to compress and move using only xp_cmdshell but it doesn't get around the problem of deleting old files. Basically I want to "fire and forget" without having to do regular maintenance. Any ideas?
Topic Locked
Posted - 07 January 2006 : 13:22:26
