Database Backups (To Disk or not To Disk)

Posted: November 3, 2008 in Uncategorized

 

Well I will start by say, if you do not know this yet SQL Server backups should be handled differently than you would your OS. Just backing up your files on your server does not keep the transactions clean. SQL Server has a number of events like check points, T-Log backups and such that will determine where the data is in your database. With that being said it’s really not so much what this entry is about.

Assuming that everyone is running good backups the question that I pose today is should you back up to disk, or let an external system back you data straight to tape. Many of the tape backup systems have add on’s that you can buy or may even come with the tape system that allow the backup system to have hooks into the database and to backup the database without the backup every hitting the disk.

So here are the options that we have:

  1. Back up’s of your SQL Server go to either locally attached storage or a disk somewhere via UNC path. It can be either a Native SQL Server backup or you could use some sort of compression tool, like Lite Speed or SQL Backup by RedGate.
  2. Your tape system has hooks into your SQL Server that backs up your database, the only time that you backup would be on “disk” is when it may be in a buffer pool that will be in a format that is readable only by the backup tool.

Here is my opinion, and let me first say that I am a DBA, I have not worked with tape systems in my career other then years ago when we just attached a tape drive to each server. So I my opinion may be a little tilted. With that being said I believe that backups should be done in a format and should reside on disk before a tape system of some sort moves them off to the tape pool. Here is the reason for my choice.

  1. Most DBA’s that I know do not know the internal workings of the tape systems that the companies they work for use. The point that I am trying to make here is that either the DBA is going to have to learn the tape software or it will take multiple people to restore a database. Assuming that your tape person does not know SQL Server.
  2. Control –There has to be something said for who has control over the backups. When it comes to databases for some reason it makes perfect sense to me that the DBA is that person. If the DBA can just put the backup file in a location and then let the tape staff know where it is then a backup can be made from there. (I do understand that if we lose that if the store location is on the local machine and that machine goes down then we still could require two people to restore that database server. But many restores that I have done have not been because of hardware but rather something done wrong internal to the database like bad data or corrupt database)
  3. Speed – Again I may have a tilted view here. From the tests that I have seen, when I backup to disk it has always been faster than if we were to run a Outside backup tool that took the database from the database straight to tape. I know that tape speeds have been improving and I in no way suggest that anyone just backup to a local disk and leave it there. From the local disk there needs to be a copy made somewhere, but it’s not inside the database when that copy is being made.

I would love to hear what you think about this and how you may be doing in differently. Technology is changing all the time. And I am not sure that I may just be stuck in my old way of doing things and I am being a little hard headed about it.

Comments
  1. I protect my files and emails with a very easy to use data backup software called Titan Backup, the destination is a 500gb WD harddisk, but the company stated that an online feature is under work also. Give it a free trial here.

    I also got my hands on a 30% discount coupon from them, during the order process enter this coupon:NEOB-M5VL

  2. Dan Tan says:

    I agree that disk has advantages to going direct to tape.

    My thoughts are:
    1) Separation of tasks: in the small company where I work, I often run a backup of a database before I make a significant structure change, dumping it into a directory that is picked up nightly by the tape backup. Our admin who handles the tape backups has a simple task to schedule– pickup any new files in the backup subdirectory. And I have the advantage of being able to access the backups quickly without having to grab our backup admin (who is also our unix/windows/network/firewall/email/desktop admin).

    2) Speed: A long time ago I restored an entire SQL Server from tape. It was only a few gigabytes of information total, but the restore ran all night long due to the speed limitations of tape.

    After that, I moved all backups straight to disk, and have done server clones that run in a fraction of the time it takes to do a tape restore.

    3) Redundancy: our tape backup software has hooks to pull SQL backups straight from the SQL Server. It worked great for a while, but one day the tape backups had problems. Suddenly, we had no fresh backups of our databases, because the tape backups were failing.

    Backing up to disk first allows us to have fresh backups, even if the tape backups fail.

    Those are my thoughts…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s