Dan Goldberg wrote: > I just got an MS-SQL database dropped in my lap (yech). Anyone have a > working strategy to backup ms-sql with bacula? It does not need to be a > hot backup
Best way I've found is to use MS SQL's built in database backup settings to dump databases (and transaction logs - a backup seems to be about the only way to stop these growing ad infinitum...) to files and then direct bacula to backup those. You'll find that if you compress the SQL server backups (using gzip, say) then they'll shrink to around 10% of their original size. Doesn't matter so much if your tape drive does hardware compression, which ours do, but I use this so that we can keep a week or so of SQL Server backups available 'online'. My setup is as so: Under "Maintenance Plans" I have a plan which does a complete database backup to disk (J:\SQLBACKUP) of selected databases (I don't bother backing up test databases) with a verify and a 'remove files after 7 days' option. This runs at about 11pm, the same time bacula kicks in. It doesn't take very long to dump the databases to disk - the server's never very busy. At this point, bacula's off backing up other servers. At quarter past midnight I run a script which gzips and prunes all files in J:\SQLBACKUP (this could be run as a ClientRunBeforeJob, but it takes a while to run and I expect it'd hold other jobs up while it ran). This removes all *.gz files which are more than 7 days old (SQL Server probably won't do this, despite the setting in the maintenance plan, as gzip will have renamed the file to $file.gz) and then compresses any .BAK and .TRN files it finds (using nice gzip -9 - without nice, it *really* slows the server down). I think I've posted the script which compresses and prunes SQL Server backups here in the past.. if you can't find it in the archives, give me a shout. I have Cygwin installed on the SQL Server machine so that I can write the script in bash (although you could install a win32 port of gzip and write it in Javascript or VBScript, I guess)... This setup seems to work well for me. If we need to restore a database, then we have a week's worth in J:\SQLBACKUP and they just need gunzipping and restoring. If we need to go back more than a week, then we can pull out a tape, restore the .gz file, gunzip it and then restore it to the database server. If I didn't want to keep the online archive of databases then there wouldn't really be much point in gzipping the backups, but I feel that going to tape should be a last resort. As it is, I think I've had to restore a database exactly once in the last year, because someone did something silly. -- Russell Howe [EMAIL PROTECTED] ------------------------------------------------------- SF.Net email is Sponsored by the Better Software Conference & EXPO September 19-22, 2005 * San Francisco, CA * Development Lifecycle Practices Agile & Plan-Driven Development * Managing Projects & Teams * Testing & QA Security * Process Improvement & Measurement * http://www.sqe.com/bsce5sf _______________________________________________ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
