A Typical Backup and Restore Scenario

To illustrate a typical backup and restore scenario, let's look at the fictitious Northwind Trading Company. Northwind is an international company supplying a wide variety of food products to stores across the globe. Because of the different time zones, order personnel staff the phones 24 hours a day, 7 days a week. Grocery retailers call in their orders, they are entered into the database, and then that data is used to fill, ship, and bill the order. As all inventory, orders, and billing information is stored in the database it is considered mission critical. In other words: No database, no Northwind Trading Company. Therefore, a backup plan is in order.

In your analysis of the database, you discover a few key points. The server is configured with two disks. Drive C contains the OS and the SQL Server 2000 installation, and drive D holds the Northwind database and transaction log (not an ideal disk configuration, but you will have to deal with that another day). As the database must be available 24/7, you will perform online backups . You test a backup and discover it takes just under three hours to do a full backup. Querying the order entry staff, you discover that although orders come in around the clock, there is a slow period as the day traverses the Pacific Ocean. This lasts from 1:00 a.m. until 6:00 a.m. local time. With this information, you decide to do a nightly backup at 1:00 a.m. With this plan you can always restore the database to the state it was in at the time the backup completed.

The manager of the order entry department brings to your attention that in this scenario, if the database were to fail at the end of the business day, and the database was restored with the nightly backup, the amount of orders lost would be ruinous to the company. Further questioning reveals that, on average, the amount of orders processed in an hour could be recovered manually by having the data entry people work overtime to call their clients back. Armed with this information, you decide to implement a transaction log backup every hour on the hour . Now you can recover the database up until the time of the last backed up log. Actually, if the current log is not damaged, you could back it up and recover right up until the time of failure!

The following week, at 3:33 p.m. on Tuesday, the database crashes. Your investigation reveals that drive D (remember the one with all the data and transaction logs?) has failed. You replace drive D and restart the system. Because the active transaction log was on this disk, it is unrecoverable, so you will lose all transactions from the time you did the last log backup (3:00 p.m.) and when you crashed (3:33 p.m.). You now proceed to restore the full backup from that morning, and apply all transaction log backups taken since the full backup. When you bring the database on line it is current to 3:00 p.m., and the order entry people get busy calling back clients who placed orders after that time. Some customers understand and reorder; however others are quite irate and vow to deal with Acme Trading Company in the future.

The following morning at the "Post Mortem" meeting, when you finish patting your back for a job well done, the Accounting Manager informs you that one of the customers who went away mad was also your largest customer. He wants to know how you could have done a better recovery. The issue is the loss of the orders between 03:00 p.m. and the time of failure. Had you separated the transaction log file from the data files, by placing it on a separate drive (costing less than $1,000), you could have backed up the current log before you did the restore. This would have given you the ability to recover up until the time of failure. Had the data files and logs been on mirrored drives , the failure would not have affected production at all. The accounting manager approves the purchase of new disks on the spot!