Chapter 3. Disaster Recovery Techniques to Protect Your Database

After completing this chapter, you will be able to:

• Decide which database backup strategy you need

• Configure the right recovery model

• Perform full database, differential, and transaction log backups

• Restore user and system databases

• Schedule backups

The previous chapter considered securityprotecting your data from unauthorized users. This chapter will look at protecting your data from unanticipated loss. Preventing data loss is one of the most critical issues involved in managing database systems. Data can be lost as a result of many different problems:

• Hardware failures

• Viruses

• Incorrect use of UPDATE and DELETE statements

• Software bugs

• Disasters, such as fire or flood

To prevent data loss, you can implement a recovery strategy for your databases. Recovery strategies need to be planned, implemented, and tested according to the possible failures that can occur and the level of protection needed for the data. In a data warehouse where data can be recovered from other systems, it is probably unnecessary to back up every single transaction. To have full backups of the data in regular time intervals might be enough. On the other hand, for a database storing online shop transactions, you might want to back up every single transaction. SQL Server provides rich functionality to implement the type of backups you need. In this chapter, we will discuss the most common strategies used in Microsoft SQL Server to protect data.