Long Live Tape
Your back-up plan probably relies on something that's been around for ages: tapes. Here's what you need to keep in mind when managing this old but reliable technology.
- By Eric Johnson
- 11/07/2007
In the first two parts of this series we looked at backup types and how to select a backup strategy. In this part, we finally talk about tape backups and how to use them in your environment.
I've never seen an environment or met a DBA that did not have to consider how to set up tape backup for SQL Server. There are, as with everything else, many options for setting up tapes backups and we'll look at the two most common and talk about the pros and cons of each.
Backing up the Backups
This is probably the most common approach you will see for backing SQL Server databases to tape. The DBA sets up the backup strategy in SQL Server and backs everything up to disk devices. The Windows or Backup administrator then “sweeps” the SQL backup files off to tape on some regular schedule. This method is easy to set up and pretty straight forward.
In the event of a failure, the more recent backups are still on a hard drive attached to the SQL Server. This allows the DBA to immediately start a restore with the available files.
The big disadvantage to this method is that, depending when your files get taken to tape, a catastrophic failure could cause you to lose your database and the most recent backups. The other problem is that of the double restore. If you have to restore the database from something other than the most recent backup, you have to restore the backup files from tape before you can restore the SQL Server database.
Backing up Straight to Tape
With this method, the SQL Server database gets backed up directly to a tape. This can be done with a local tape drive or with third-party backup solutions. This method solves the problems of backing up your backup, but it also removes the advantages you get with the first method. In fact, the straight-to-tape backup method is almost an exact opposite of the first approach. When backing up straight to tape, you no longer have to worry about a failure that causes you to lose both the backups on disk and the database, and you can typically perform your restore straight from tape eliminating the double restore problem. But you lose the flexibility of having the restore files on the server ready for you use. This can mean waiting for a tape to be loaded into a tape library, which can cause a delay.
I won’t tell you which method to choose; it really depends on what is more important to you and your environment. I will say that in most environments I am involved in, I opt to use the first method. I like having the backups available and, like many DBAs I know, I am a bit of a control freak and I don’t like having another group stand between me and my backups.
SQL Server Help —
Just An E-Mail Away |
Need SQL Server troubleshooting help? Or maybe you want a better
explanation than provided in the manuals? Describe
your dilemma in an e-mail to the MCPmag.com editors
at [email protected];
the best questions get answered in this column and garner
the questioner with a nifty Redmond T-shirt.
When you send your questions, please include your
full first and last name and location with your message. (If you prefer to remain anonymous,
specify this in your message, but submit the requested
information for verification purposes.) |
|
|
I hope this series has provided you with some insight into SQL Server backups. There is obviously more to learn then we have covered here, but this should give you enough information to get you started.
Next time, we'll talk about scripting logons.
About the Author
Eric Johnson, SQL Server MVP, is the owner of Consortio Services in Colorado Springs providing IT systems management and technology
consulting. He is also the President of the Colorado Springs SQL Server
User Group. He can be contacted at www.consortioservices.com.