12 Backups

 

This chapter covers

  • How to align backup strategies with organizational requirements
  • The importance of testing backups and how to do this in a practical way
  • How database snapshots and storage snapshots can complement a backup strategy
  • The importance of considering extract, transform, and load processes when scheduling backups
  • SQL Server’s three recovery models and when each is most appropriate
  • Security considerations for database backups

In this chapter, we will discuss common mistakes that database administrators (DBAs) make when planning and implementing backups. Over the years, I have seen a tendency for backup strategies to be treated as something of an afterthought. The value of backups can be difficult to quantify—until such a point when we urgently need to recover a database, of course.

12.1 #78 Not considering RPO and RTO

12.2 #79 Using database snapshots as a recovery strategy

12.3 #80 Using crash-consistent snapshots as a recovery strategy

12.4 #81 Not testing backups

12.4.1 Checking that backups completed successfully

12.4.2 Verifying backup integrity

12.5 #82 Taking backups during an ETL window

12.6 #83 Always using the FULL recovery model on data warehouse and development systems

12.7 #84 Using SIMPLE recovery model for OLTP databases

12.8 #85 Not backing up after changing recovery model

12.9 #86 Scheduling log backups immediately after a full backup

12.10 #87 Not using COPY_ONLY backups for ad hoc backups

12.11 #88 Forgetting that backups are part of our security posture

Summary