We have been using a sql job to back up our SQL 2005 databases locally, and then copy them to tape with BackupExec. At the same time, we use MS Data Protection Manager to take incremental backups throughout the day. The tapes are just a nightly backup that we take offsite, and DPM allows us to restore from any 15-minute increment (or nightly Full backup to restore faster). Our most common use is to restore DBs to development servers, which we do several times a day. We're looking to replace DPM, and we are also upgrading BackupExec.
We will continue to use BackupExec as our disaster recovery backups, but I need a replacement for DPM that will primarily be used for putting copies of databases onto development servers. Ideally, I'd like to find a solution that does the incremental backups every 15 minutes or so, and allows me to restore to any time to any server like we do now. In addition, I would prefer if I could automate restores, and/or grant other users access to restore a database to server(s) they have access to. We would like to store copies for weeks or months (probably just a Full copy a day for anything after a week), or basically as long as local disk will let us.
I'm beginning my Google searching today, but I was hoping to hear some expert opinions from here, too.
Have you looked at Quest Litespeed? I haven't used it, but I know it's pretty big in the SQL backup space.
I always have to hesitate to answer my own question, but I didn't get the software we went with as an answer and I think it's the best one for my requirements. We went with Idera SQLSafe. It efficiently takes backups of SQL Server 2005 and 2008, and schedules the backups throughout the day (differentials during the day and then a full daily). A nice bonus feature it has is storing all of the backups in a central location, so I don't need to worry about storage on the actual servers.
Its best abilities are in the restores though. It is licensed by instance being backed up, so I can have the agent installed on as many servers as I want, and restore to them as many times as I want. I can kick off a flurry of restores quickly through their interface and let them all run at the same time. It also gives me a command-line option to run the restores. Although I haven't set it up yet, I can give a developer the ability to run their own restores as long as they can read the backup files and have the necessary permissions on the SQL server.
Here is a question that you have to ask yourself with regard to backups.
The answer to that question will drive what, how and when you back up. In a worst-case scenario (i.e.: you lose your entire building at 5pm), the nearest backup you could ever hope to restore was from last night, losing an entire days worth of work...presumably.
Backup exec is a very good product for backing up. We don't use it anymore but when we did, using the SQL Agent was a great means of backing up not only system state, but the databases as well. It would not be very efficient for backing up every 15 minutes. We use Red Gate Sql Backup along with an over-the-wire service for backups now. The entire process for us is automated.
I have to ask, how are the tapes being taken offsite? Please don't tell me it's one of the I.T. guys just to be nice and save the company some money.
Also with regard to backup/restore to another server for development purposes: There are other more efficient and automated ways of doing that without resorting to backup/restoring. Specifically replication (snapshot)