Read an Excerpt
Chapter 3: Backup and Recovery
We would argue that this is the mostimportant chapter in this book. Too many people overlook the importance of implementing a backup and recovery plan. If you read this chapter and then feel that this stuff isn't for you, that's fine, but do read it before making that decision.Before We Get Started
Our goal for this book is to show how you can use SQL Server to add value to your company's operations. We have tried to accomplish this goal by showing you realistic projects that can be used in your business' daily operations. Explaining backup and recovery in a "project" format is almost impossible because of the background you need before you can actually complete a project. So, we've devoted a good number of pages that explain backup and recovery concepts and processes and have delayed the actual hands-on project to the final pages of this chapter. Yes, this format is a little different from our other chapters, but we feel it's the best way to ensure that you understand how to properly plan and implement a backup and recovery strategy.
Needs Backup and Recovery?
I (Garth) am one of the lucky ones. My first job as an IT Professional was under the direction of a gentleman who believed that losing data was simply unacceptable. I was taught to review the daily backup logs to ensure that they completed without errors, to rotate tapes on a frequent basis, to periodically test the validity of backups on another computer, and to store tapes off site so that in the case of a complete catastrophe, the most that we would ever lose was one week's worth of data. I thought that this was simply the way it was done surely everyone took as much care of their data as we did of ours. Well, when I moved to my next position, I discovered that this wasn't the case. As a matter of fact, I've had several positions since then and only one of those subsequent companies matched the first company's rigorous approach to backup and recovery. A couple of the companies didn't even realize the danger they were in by having such poor backup and recovery processes.
Hopefully, by the end of this chapter, you'll not only be able to adequately assess the value of your data-even show your boss the numbers if you need to-but also prepare a planned and tested backup and recovery strategy that will ensure that your chances of data loss are minimized. As the saying goes, this stuff isn't rocket science. Yes, it's sort of boring (just as boring as insurance is until a tree falls on your house or you need major surgery), but it pays to be prepared. One of the first things you need to realize is that hardware failures aren't the only potential causes of data loss. Although most of us prepare for hardware failures, your backup and recovery strategy should also address each of these potential disasters:
- Hardware failure
- Natural catastrophe
- Malicious destruction of data
- Accidental destruction of data
The most common hardware failure to protect against is the loss of one or more of the hard drives on which your data is stored. Hard-drive failures can be the result of power surges, power spikes, or a design or manufacturing flaw in the drive. The most damaging event that you need to protect against is a natural catastrophe, which can include fire or flood and result in a complete loss of your facility. Malicious destruction of data can occur when a disgruntled employee decides to exact revenge by deleting data or setting a "trap" so that the database is rendered useless after they leave. Accidental destruction can occur when a developer or end user leaves the WHERE clause off an UPDATE or DELETE statement, has trouble understanding the finality of TRUNCATE TABLE, or other non-logged events. Understanding the back up and recovery capabilities of SQL Server 7 is key to ensuring that your data is recovered when you're faced with any of these events.
If you aren't familiar with truncate TABLE pIease read on. removes all data from a table without recording the process in the transaction log. In other words, it deletes all of the data in the target table and the only way to recover it is to restore from the last good database backup.
The rest of this chapter focuses on:
- Placing a dollar value on your data
- The components of SQL Server 7's backup and recovery technology
- Creating a database backup
- Restoring a database from a backup
You Look Like You Need an Overview
SQL Server 7 provides several options for data backup and recovery. With so many options, it can be confusing to know which one to choose. Before the various system characteristics that dictate a particular backup and recovery strategy are discussed, a very general understanding of the backup options that are available is necessary. Each of these options are covered in great detail later in this chapter, so don't be concerned if you do not have a complete understanding of each option after completing this section. The immediate goal here is to give you a general idea about processing time, size requirements, and the ability to perform point-in-time recovery for each option.
The database backup is a complete backup of a database. It's the most time-consuming type of backup, requires the most space, and doesn't allow point-in-time recovery.
The differential database backup records all of the modifications that have occurred since the last database backup. This is less time-consuming and requires less space than the database backup, and also doesn't allow point-in-time recovery.
The transaction log backup records all modifications that have occurred since the last database or differential database backup. Used correctly, it's the fastest backup option, uses the least amount of space, and does allow point-in-time recovery.
How Much Can You Afford to Lose?
Have you ever asked a non-technical manager how much data they can afford to lose or how long they can afford to have their system down? If you've asked more than one, we'd be willing to bet you lunch that at least one of them answered with "none" or "zero time." Well, implementing a strategy that ensures no data loss and that system downtime is reduced to seconds is extremely expensive ($10,000+) and requires advanced skills to install and configure. These types of systems-referred to as highly available, hot standby, or failover systems-implement clustering supported only by SQL Server Enterprise Edition and are not addressed in this book. If you require this level of recovery, please see the Books Online topic, Using SQL Server Failover Support. In this chapter, we cover the strategies that apply to the majority of companies using SQL Server 7...