jpSQLDude (2/20/2010)

---

I have many servers I have to back up.

I am using Maintenance Plans to create files, but then I use batch files and Scheduled tasks to drive 7-Zip and Robocopy to do the work of compressing, encrypting and moving those files off the server.

But batch files are ridiculously limited -- there is no real error checking or handling. They work just fine when they work, in the best-case scenarios, but when things go wrong you might not even know about it, and then you don't have any backups. You most especially need backups to work in the worst-case scenarios!

So I was wondering: What tools other than batch files/command-line do you use to automate your admin tasks?

For example, how about VBscript? My concern with that is what allowed the ILOVEYOU virus, and may be a security risk and disabled on some production servers. I want my backup system to work on all servers.

How about PowerShell? I believe it is totally free, but it requires the .NET framework, and besides, at the enterprise where I work getting anything new approved to be installed onto a production server is a nightmare that takes months (at best).

And there are commercial tools like WinAutomation that seems to be exactly what would work for automating admin tasks, but again not only would I need to justify installing it, I'd have to make the case for buying it.

Once you create backups to files sitting on a hard drive, then what do you do??

Just FYI, here is a sampling of what I am currently doing for my backups:

I use Maintenance Plans to just do regular backups to files right on the same server.

Then I compress and encrypt those files with 7-zip (using a batch file and Scheduled Tasks). Then I copy all those small/encrypted files over my network to a central file server (using a batch file and Robocopy and Scheduled Tasks). I do hourly Log backups, so my loss-exposure -- even if the server blows up -- is 1 hour. (Some servers are even tighter.)

Then I also copy all those files across the network again to a big hard drive attached to my workstation (also with a batch file and Robocopy and Scheduled Tasks).

And finally I pull all those files to tape. If you don't have any backups, you can be -- and should be -- fired. No one ever gets fired for making too many backups!! 😀

The net result is I have a boatload of backups, copied all over the place, they are secure since they are encrypted, and I have a copy on my workstation so I can easily do restores and actually test the backups and validate they are working (and document the recovery process, etc, etc).

Its a pretty good system, when it works, which it usually does, but what happens if a server loses power during a really long Full backup?? A corrupt .BAK files is created, gets compressed, mirrored all over the place, and I think I have a backup but I don't!! Many similar ugly scenarios when relying on Batch Files..........