Chapter 7: SQL Server Disaster Recovery

Overview

Managing the disaster recovery (DR) plan for a data center is one of the most exciting roles in IT, if you like adventure, like bungee jumping or skydiving. I know this firsthand because I have been doing DR for some years now; you generally get the budget you ask for if the business’s health absolutely depends on the servers you maintain (which is most of the time). In Miami, where I worked for some years, as soon as a hurricane watch goes into effect, the CEO hands the business to you. Now you’re in charge and you had better have a good DR plan.

That said, disaster recovery is probably the DBA role (developers, you can skip this chapter if you are lucky enough to be part of a SQL Server team) that requires the toughest skin on an individual or the group. In businesses that can afford it, some DBAs do nothing else but manage DR and backup/restore. When disaster strikes, and it does, the future of the business, and everyone’s job, is on the line. If you do things right and get the databases back online in record time, you are pretty much the darling of the company; if you fail, you’ll be out the door in the time it takes to spell “rollback.” Over the years that I have managed DR, I have seen several SQL Server databases go to the wall, and several Exchange installations go south too, and dozens of smaller database corruption issues, and so on, You have to expect it; you have to plan for it.

While we recovered every time and saved the day (except one situation I will relate to you later), the most important piece of advice I can give is that you can never be fully prepared for your day in hell, no matter how much you practice. Many things can go wrong in the heat of disaster. I once had to ask a CEO to leave the computer room because I thought he was going to get sick all over the servers.

This chapter will introduce you to some DR theory and how it applies to SQL Server 2005, especially with respect to backup and restore of SQL Server databases and their transaction logs. It will introduce you to the tools that ship with the product and the recovery features inherent in the system. It will also take you step-by-step through several backup and restore procedures, and describe techniques and practices to ensure database and transaction log availability.

For the most part, this chapter assumes you are supporting a high-end data center application, and that recovery and restore will be automated and highly sophisticated, However, with this chapter you will also learn how to use the power of SQL Server’s integrated visual backup/restore features to manage smaller shops, in which you are required to perform daily backups, and to undertake manual recovery, such as by reentering lost transactions.