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:
- 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.
- 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.
- 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.
- 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)
- 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.