Microsoft® SQL Server® 2012 Bible

547

CHAPTER

21

Backup and Recovery Planning

IN THIS CHAPTER

Understanding the Recovery Models

Backing up User Databases

Understanding and Working with the Transaction Log

Recovering a Lost Database, a Lost System Database, or the Entire Server

T

he foundation for this book, the Information Architecture Principle (introduced in Chapter 2,

“Data Architecture”), puts into words the reason why there must be a solid recovery plan:

Information is an organizational asset, and, according to its value and scope, must be organized,

inventoried, secured, and made readily available in a usable format for daily operations and analysis by

individuals, groups, and processes, both today and in the future.

It goes without writing that for information to be “readily available...both today and in the

future,” regardless of hardware failure, catastrophes, or accidental deletion, there must be a plan B.

Obviously, this is an imperfect world and bad things do happen to good people. Because you’re

bothering to read this chapter, it’s true that performing backups isn’t exciting. In some jobs

excitement means trouble, and this is one of them. To a good DBA, being prepared for the worst

means having a sound recovery plan that has been tested more than once.

Consistent with the fl exibility found in other areas of SQL Server, you can perform a backup in

multiple ways, each suited to a different purpose. SQL Server offers three recovery models, which

help organize the backup options and simplify database administration.

This chapter discusses the concepts that support the recovery effort, which entails both backup

and restoration. It seems foolish to study backup without also learning about how restoration

completes the recovery.

Recovery planning is not an isolated topic. Transactional integrity is deeply involved in the theory

behind a sound recovery plan. After you determine the recovery strategy, it’s often implemented within

a maintenance plan (Chapter 22). Because recovery is actually a factor of availability, the high availability of log

shipping (Chapter 26), database mirroring (Chapter 27), and failover clustering (Chapter 29) is also a factor in recov-

ery planning.

c21.indd 547c21.indd 547 7/31/2012 9:22:43 AM7/31/2012 9:22:43 AM

http://www.it-ebooks.info