Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
    
+1(5) from developer. Good question! –  Gennady Vanin Геннадий Ванин Aug 13 '10 at 16:31

3 Answers 3

up vote 1 down vote accepted

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.

share|improve this answer

Have you looked at Quest Litespeed? I haven't used it, but I know it's pretty big in the SQL backup space.

share|improve this answer
    
+1 This was going to by my answer. Litespeed is awesome. We use it to backup SQL to disk and then NetBackup to get it to tape / offline (I've also used BackupExec and others, whichever you prefer for filesystem). Backups get scheduled as SQL Agent jobs so you have the flexibility that you have with the Agent scheduler. Compression and encryption are also there, all kinds of things. In fairness, Redgate has SQL Backup as well which is comparable, but I like Quests's tool UI's better. Quest sales is quite good to work with in my experience, even for small purchases of 1-2 licenses. –  squillman Aug 13 '10 at 14:57
    
I'm going to be looking at this option. The three that I've come across so far to dive into further are Litespeed, Redgate SQL Backup, and Idera's SQL Safe Backup. –  Paul Kroon Aug 13 '10 at 15:38

Here is a question that you have to ask yourself with regard to backups.

  1. If you have to restore to the last version you have, what is the most business you could live with losing? A day? A week? A month? No more than 15 minutes ago?

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)

share|improve this answer
    
I should reword my question to ask about restoration solutions. We are sticking with BackupExec and the same plans for the backups we would use for disaster recovery, but I'm mainly wondering about how to handle our development servers. Replication is definitely an option for having one server that is just a delayed copy of production, but we need the ability to restore any other server to any nightly backup going back weeks or longer (as many as we can store). –  Paul Kroon Aug 13 '10 at 14:53
2  
If it's a business database and you're taking tapes offsite...don't forget to encrypt them :-) –  Bart Silverstrim Aug 13 '10 at 14:55
    
@Bart - Definitely; keeping the key used for decrypting offsite and separate, too. –  Paul Kroon Aug 13 '10 at 14:59
    
@Paul: Interesting scenario that you have. So snapshot replication wouldn't fit your needs then? –  GregD Aug 13 '10 at 15:05
    
@GreD - It might for a few cases, but not everything. I typically need last night's production copy for developers and QA testers (exact copies), and our SQL Devs need it for their own development. Also, we'll occasionally need older copies for troubleshooting issues with either our applications or data (I've had to go months back before). We heavily customize an ERP system we bought, which leads to lots of interesting issues. :) –  Paul Kroon Aug 13 '10 at 15:31

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.