grant fritchey - common problems in backup and recovery and how to handle them

Common Backup ProblemsGrant Fritchey

#sqlinthecity

2

Goals

• Identify common backup issues• Prevent those issues from occurring

3

Agenda

• Disaster Recovery• SQL Server Backups• File Backups• Drive Space• Full Transaction Log• Differential Only• Broken Backup Chain• Backup Load• Backup Testing• Offsite Storage

4

Grant Fritchey

Product Evangelist for Red Gate Software Email: grant@scarydba.com Twitter: @gfritchey Blog: scarydba.com

5

Disaster Recovery

6

SQL Server Backups

01011001

7

File Backups

Data Log

01011001

8

Drive Space

9

Full Transaction Log

10

Differential Only

11

Broken Backup Chain

12

The Wrong Server

13

Backup Load

14

Backup Testing

15

Offsite Storage

16

Goals

• Identify common backup issues• Prevent those issues from occurring

17

Resources

• Scarydba.com/backup• SQL Server Transaction Log Management

Gail Shaw & Tony Davis• Preventable Backup Errors: http

://www.simple-talk.com/sql/database-administration/7-preventable-backup-errors/

• Backup and Restore for the Accidental DBA: http://www.simple-talk.com/sql/database-administration/sql-server-backup-and-restore-for-the-accidental-dba/

• SQL Server Backups: http://www.simple-talk.com/sql/backup-and-recovery/sql-server-2005-backups/

18

Questions?

How would you… ?

What happens when… ?

Why does… ?

When do I… ?