Is it a good idea to back up SQL database to an External USB Hard Drive?

I will be installing a Database System on an Offshore Oil Platform (rack Servers). They have very limited hardware and space therefore sending up a backup infrastructure is not possible. Sending backups via FTP too is not possible as they do not even have a file server.

I was thinking of backing up the SQL database on to a Portable USD Hard Drives. The USB Hard Drives will be plugged in directly on the server at all times. Once a week, they will swap the hard drive with a fresh /older one.

Is it a good Idea to do this? If not can you propose a better solution?

It's just an idea, but you can provide an external (or third-party even) backup service trough a mirror server/db. The information will be replicated at everytime you do an insert/update/transaction log/alter/create/etc., any modification. So the transactions are very little and can be on an queue if you doesn't have access or you have a limited connection (as one connection per week) to internet / external network.

I could help you significantly more if you tell me what kind of DB are you using. It's MySQL? MSSQL? ORACLE?

Even more (just another idea) did you think on making a good-policy programability backup? For example, if you know the structure and the data, and the time of expiration of the data, you can move/delete your innecesary history or work with the above idea about a mirred db.

I think the cost of working with a mirror db with a minimal connection is less than working with external removable hardware, in terms of risk, security, space and hardware damage.

Edited:

About mirror a server there is a lot of help and tutorials and video tutorials about. My skill is more oriented to MySQL server on linux but I can tell you some tips, I hope them can help you.

• First of all, looking here, on serverfault, or even better: Database Administrators on Stack Exchange
• On the msdn for MSSQL (I give you the 2005 version, I don't know what version is your server/db: Database Mirroring in SQL Server 2005 but you can change it on the top menu.

• There is a FAQ link at bottom of that document that can clarifies you a lot some principal questions about (for example, the queue transaction, the network capacity, etc):

• Or How to prepare a mirror database on 2008 R2 (you can change the version clicking on the top, "Other versions".

• On google with keys like this one you will be tired of reading about on it: Google Search . As I told you, is not my prefered DB so I don't know really how can you do it on MSSQL, but I'm sure of one thing: if the DB is at least 2005 you can do it, and for sure with more options and better that I told you.
• I think almost every hosting that provides a service with only database server, can be do a mirror from any location with some kind of policies or certificates, they will explain it to you. I don't remember right now a good service but I know there are a lot. It's possible that you will find more cheap MySQL servers than MSSQL servers but I think you need some "migration app" to move data from MSSQL to MySQL, so for me the first option is that you look every detail about your source and then ask for a compatible mirror.

And another idea that becomes with that line of thinking:

Supose you can't configurate the mirror or anything the reason. In the last instance you can make a little job/task/automatization software or script that reads a lot of data trough SQL and send it trough SQL (and you can change the destination, for example from MSSQL (your actual source) to MySQL (that is free and you can support it wherever, with your own pc/server/etc.

Really if you know about SQL you can do it.

• And another tip, I think by that you told that you can't add/attach any HD, but you can work with RAID's disk to expand the capacity of your internal backups.

  But in any case, the main idea of a backup is that you have the backup in a complete different environment (another place) that you can access and/or switch your databases if one drops for any reason (a fire, an earthquake, a revolution, a hardware problem). That's the main idea of a mirror: a load balanced server that is clear for the user, you really don't know where are you working, it works at the same time at the same way. That's a difference with a "history backup" and a "real-time backup".

Edited 2: In any case I never answer anything about your idea. Of course is better than nothing, but for critical data is like nothing. It has a lot of non desirable future issues:

• Velocity of USB
• Manual process (by who? is the information too much critical for handling by another one? you have any legal/work/economical responsability if the person that works with your data stole / destroy / change it?
• Time, of course you want an automatically option.
• You will backup.. what? what amount? what kind of data? what is the old data? With wich criteria? The criteria will or can will change? And that becomes with the next question:
• Here in my country we have a "proverb" or "byword" that says something like... "is bread for today, hungry for tomorrow". I mean, you will need change this process at any time? Is possible that you need looking for another way in a short time? What will be the cost? Is your solution flexibility or you will get a future problem for free?

A backup to a USB connected drive is certainly better then no backup. A backup to hardware specifically designed for backups (like tapes) might be an even better choice.

Can you afford to lose a weeks worth of data? If you are only swapping drives once a week, then you are risking a weeks worth of data. If the drive is connected for a week, there is a risk of some electrical surge taking out both the computer and the backup drive. If that drive hasn't been changed for 6 days, then you lose 6 days worth of data.

Will you be shipping the drives to another location? It seems likely that the chance for some kind of event that will destroy both the original database, and the backup drives is pretty high.