�ݺ�ߣ

I got 99 problems but my backups
aint one
Richard Douglas

If you're havin'
maintenance problems
I feel bad for you son
I got 99 problems but
my backups ain't one
2

Backups

Don��t have a
��Backup
Strategy��
3

Backups

Global Marketing

Agenda
? Why do we need backups?
? Backups available in SQL Server
? Backup Strategy vs Restore Strategy
? How to make your backups faster
? How to make your restores faster

4

Backups

Have a
��Restore
Strategy��
5

Backups

Your host
? Richard Douglas
? Systems Consultant

? SQL Server MCITPro
? Maidenhead SQL User Group Leader
? Blog: http://SQL.RichardDouglas.co.uk
? Twitter: @SQLRich
? Email: Richard.Douglas@Software.Dell.com

6

Backups

7

Backups

Seriously,
think about it.
A ��Restore
Strategy�� makes
so much sense!

Why do we need backups?
? Protect valuable data
? Provides the potential to return to a ��point in time��
? Confusion between Disaster Recovery and High Availability

? Backup Myths
Image backups are sufficient
Snapshots are sufficient
Differential backups are incremental
System databases only need to be backed up once a week
Only need to keep my last backup
Buffer pool usage

8

Backups

Have a
��Restore
Strategy��
9

Backups

Recovery Models
?

Simple

?

Full

?

Bulk Logged

Data
File
Log
File

SQL
Database

Automated administration Manual Administration

Note: Recovery model will be defaulted to the one in the Model database
10

Backups

Have a
��Restore
Strategy��
11

Backups

Backup Types
?
?

Differential

?

Log

?

File / Filegroup

?

12

Full

Tail of the log

Backups

Have a
��Restore
Strategy��
13

Backups

Backup Strategy vs Restore Strategy
Backup Strategy main considerations

Restore Strategy main considerations

? Time taken to perform a backup

? Time taken to restore data
aka Recovery Time Objective (RTO)

? Cost associated with longer
maintenance window

? Data loss acceptability
aka Recovery Point Objective (RPO)

? Cost associated with storing
backup files

? Cost associated with system
unavailable

14

Backups

Showing you
why you need to
have a ��Restore
Strategy��
15

Backups

Full backup
M

0
0

0
1

0
2

0
3

0
4

0
5

0
6

0
7

0
8

0
9

1
0

1
1

1
2

1
3

1
4

1
5

1
6

1
7

1
8

1
9

2
0

2
1

2
2

2
3

T

0
0

0
1

0
2

0
3

0
4

0
5

0
6

0
7

0
8

0
9

1
0

1
1

1
2

1
3

1
4

1
5

1
6

1
7

1
8

1
9

2
0

2
1

2
2

2
3

W

0
0

0
1

0
2

0
3

0
4

0
5

0
6

0
7

0
8

0
9

1
0

1
1

1
2

1
3

1
4

1
5

1
6

1
7

1
8

1
9

2
0

2
1

2
2

2
3

T

0
0

0
1

0
2

0
3

0
4

0
5

0
6

0
7

0
8

0
9

1
0

1
1

1
2

1
3

1
4

1
5

1
6

1
7

1
8

1
9

2
0

2
1

2
2

2
3

F

0
0

0
1

0
2

0
3

0
4

0
5

0
6

0
7

0
8

0
9

1
0

1
1

1
2

1
3

1
4

1
5

1
6

1
7

1
8

1
9

2
0

2
1

2
2

2
3

S

0
0

0
1

0
2

0
3

0
4

0
5

0
6

0
7

0
8

0
9

1
0

1
1

1
2

1
3

1
4

1
5

1
6

1
7

1
8

1
9

2
0

2
1

2
2

2
3

S

0
0

0
1

0
2

0
3

0
4

0
5

0
6

0
7

0
8

0
9

1
0

1
1

1
2

1
3

1
4

1
5

1
6

1
7

1
8

1
9

2
0

2
1

2
2

2
3

16

Backups

Full backup
M

0
0

0
1

0
2

0
3

0
4

0
5

0
6

0
7

0
8

0
9

1
0

1
1

1
2

1
3

1
4

1
5

1
6

1
7

1
8

1
9

2
0

2
1

2
2

2
3

T

0
0

0
1

0
2

0
3

0
4

0
5

0
6

0
7

0
8

0
9

1
0

1
1

1
2

1
3

1
4

1
5

1
6

1
7

1
8

1
9

2
0

2
1

2
2

2
3

W

0
0

0
1

0
2

0
3

0
4

0
5

0
6

0
7

0
8

0
9

1
0

1
1

1
2

1
3

1
4

1
5

1
6

1
7

1
8

1
9

2
0

2
1

2
2

2
3

T

0
0

0
1

0
2

0
3

0
4

0
5

0
6

0
7

0
8

0
9

1
0

1
1

1
2

1
3

1
4

1
5

1
6

1
7

1
8

1
9

2
0

2
1

2
2

2
3

F

0
0

0
1

0
2

0
3

0
4

0
5

0
6

0
7

0
8

0
9

1
0

1
1

1
2

1
3

1
4

1
5

1
6

1
7

1
8

1
9

2
0

2
1

2
2

2
3

S

0
0

0
1

0
2

0
3

0
4

0
5

0
6

0
7

0
8

0
9

1
0

1
1

1
2

1
3

1
4

1
5

1
6

1
7

1
8

1
9

2
0

2
1

2
2

2
3

S

0
0

0
1

0
2

0
3

0
4

0
5

0
6

0
7

0
8

0
9

1
0

1
1

1
2

1
3

1
4

1
5

1
6

1
7

1
8

1
9

2
0

2
1

2
2

2
3

17

Backups

Full backup
M

0
0

0
1

0
2

0
3

0
4

0
5

0
6

0
7

0
8

0
9

1
0

1
1

1
2

1
3

1
4

1
5

1
6

1
7

1
8

1
9

2
0

2
1

2
2

2
3

T

0
0

0
1

0
2

0
3

0
4

0
5

0
6

0
7

0
8

0
9

1
0

1
1

1
2

1
3

1
4

1
5

1
6

1
7

1
8

1
9

2
0

2
1

2
2

2
3

W

0
0

0
1

0
2

0
3

0
4

0
5

0
6

0
7

0
8

0
9

1
0

1
1

1
2

1
3

1
4

1
5

1
6

1
7

1
8

1
9

2
0

2
1

2
2

2
3

T

0
0

0
1

0
2

0
3

0
4

0
5

0
6

0
7

0
8

0
9

1
0

1
1

1
2

1
3

1
4

1
5

1
6

1
7

1
8

1
9

2
0

2
1

2
2

2
3

F

0
0

0
1

0
2

0
3

0
4

0
5

0
6

0
7

0
8

0
9

1
0

1
1

1
2

1
3

1
4

1
5

1
6

1
7

1
8

1
9

2
0

2
1

2
2

2
3

S

0
0

0
1

0
2

0
3

0
4

0
5

0
6

0
7

0
8

0
9

1
0

1
1

1
2

1
3

1
4

1
5

1
6

1
7

1
8

1
9

2
0

2
1

2
2

2
3

S

0
0

0
1

0
2

0
3

0
4

0
5

0
6

0
7

0
8

0
9

1
0

1
1

1
2

1
3

1
4

1
5

1
6

1
7

1
8

1
9

2
0

2
1

2
2

2
3

18

Backups

Differential
M

0
0

0
1

0
2

0
3

0
4

0
5

0
6

0
7

0
8

0
9

1
0

1
1

1
2

1
3

1
4

1
5

1
6

1
7

1
8

1
9

2
0

2
1

2
2

2
3

T

0
0

0
1

0
2

0
3

0
4

0
5

0
6

0
7

0
8

0
9

1
0

1
1

1
2

1
3

1
4

1
5

1
6

1
7

1
8

1
9

2
0

2
1

2
2

2
3

W

0
0

0
1

0
2

0
3

0
4

0
5

0
6

0
7

0
8

0
9

1
0

1
1

1
2

1
3

1
4

1
5

1
6

1
7

1
8

1
9

2
0

2
1

2
2

2
3

T

0
0

0
1

0
2

0
3

0
4

0
5

0
6

0
7

0
8

0
9

1
0

1
1

1
2

1
3

1
4

1
5

1
6

1
7

1
8

1
9

2
0

2
1

2
2

2
3

F

0
0

0
1

0
2

0
3

0
4

0
5

0
6

0
7

0
8

0
9

1
0

1
1

1
2

1
3

1
4

1
5

1
6

1
7

1
8

1
9

2
0

2
1

2
2

2
3

S

0
0

0
1

0
2

0
3

0
4

0
5

0
6

0
7

0
8

0
9

1
0

1
1

1
2

1
3

1
4

1
5

1
6

1
7

1
8

1
9

2
0

2
1

2
2

2
3

S

0
0

0
1

0
2

0
3

0
4

0
5

0
6

0
7

0
8

0
9

1
0

1
1

1
2

1
3

1
4

1
5

1
6

1
7

1
8

1
9

2
0

2
1

2
2

2
3

19

Backups

Differential
M

0
0

0
1

0
2

0
3

0
4

0
5

0
6

0
7

0
8

0
9

1
0

1
1

1
2

1
3

1
4

1
5

1
6

1
7

1
8

1
9

2
0

2
1

2
2

2
3

T

0
0

0
1

0
2

0
3

0
4

0
5

0
6

0
7

0
8

0
9

1
0

1
1

1
2

1
3

1
4

1
5

1
6

1
7

1
8

1
9

2
0

2
1

2
2

2
3

W

0
0

0
1

0
2

0
3

0
4

0
5

0
6

0
7

0
8

0
9

1
0

1
1

1
2

1
3

1
4

1
5

1
6

1
7

1
8

1
9

2
0

2
1

2
2

2
3

T

0
0

0
1

0
2

0
3

0
4

0
5

0
6

0
7

0
8

0
9

1
0

1
1

1
2

1
3

1
4

1
5

1
6

1
7

1
8

1
9

2
0

2
1

2
2

2
3

F

0
0

0
1

0
2

0
3

0
4

0
5

0
6

0
7

0
8

0
9

1
0

1
1

1
2

1
3

1
4

1
5

1
6

1
7

1
8

1
9

2
0

2
1

2
2

2
3

S

0
0

0
1

0
2

0
3

0
4

0
5

0
6

0
7

0
8

0
9

1
0

1
1

1
2

1
3

1
4

1
5

1
6

1
7

1
8

1
9

2
0

2
1

2
2

2
3

S

0
0

0
1

0
2

0
3

0
4

0
5

0
6

0
7

0
8

0
9

1
0

1
1

1
2

1
3

1
4

1
5

1
6

1
7

1
8

1
9

2
0

2
1

2
2

2
3

20

Backups

Differential
M

0
0

0
1

0
2

0
3

0
4

0
5

0
6

0
7

0
8

0
9

1
0

1
1

1
2

1
3

1
4

1
5

1
6

1
7

1
8

1
9

2
0

2
1

2
2

2
3

T

0
0

0
1

0
2

0
3

0
4

0
5

0
6

0
7

0
8

0
9

1
0

1
1

1
2

1
3

1
4

1
5

1
6

1
7

1
8

1
9

2
0

2
1

2
2

2
3

W

0
0

0
1

0
2

0
3

0
4

0
5

0
6

0
7

0
8

0
9

1
0

1
1

1
2

1
3

1
4

1
5

1
6

1
7

1
8

1
9

2
0

2
1

2
2

2
3

T

0
0

0
1

0
2

0
3

0
4

0
5

0
6

0
7

0
8

0
9

1
0

1
1

1
2

1
3

1
4

1
5

1
6

1
7

1
8

1
9

2
0

2
1

2
2

2
3

F

0
0

0
1

0
2

0
3

0
4

0
5

0
6

0
7

0
8

0
9

1
0

1
1

1
2

1
3

1
4

1
5

1
6

1
7

1
8

1
9

2
0

2
1

2
2

2
3

S

0
0

0
1

0
2

0
3

0
4

0
5

0
6

0
7

0
8

0
9

1
0

1
1

1
2

1
3

1
4

1
5

1
6

1
7

1
8

1
9

2
0

2
1

2
2

2
3

S

0
0

0
1

0
2

0
3

0
4

0
5

0
6

0
7

0
8

0
9

1
0

1
1

1
2

1
3

1
4

1
5

1
6

1
7

1
8

1
9

2
0

2
1

2
2

2
3

21

Backups

Transaction Log Backup
M

0
0

0
1

0
2

0
3

0
4

0
5

0
6

0
7

0
8

0
9

1
0

1
1

1
2

1
3

1
4

1
5

1
6

1
7

1
8

1
9

2
0

2
1

2
2

2
3

T

0
0

0
1

0
2

0
3

0
4

0
5

0
6

0
7

0
8

0
9

1
0

1
1

1
2

1
3

1
4

1
5

1
6

1
7

1
8

1
9

2
0

2
1

2
2

2
3

W

0
0

0
1

0
2

0
3

0
4

0
5

0
6

0
7

0
8

0
9

1
0

1
1

1
2

1
3

1
4

1
5

1
6

1
7

1
8

1
9

2
0

2
1

2
2

2
3

T

0
0

0
1

0
2

0
3

0
4

0
5

0
6

0
7

0
8

0
9

1
0

1
1

1
2

1
3

1
4

1
5

1
6

1
7

1
8

1
9

2
0

2
1

2
2

2
3

F

0
0

0
1

0
2

0
3

0
4

0
5

0
6

0
7

0
8

0
9

1
0

1
1

1
2

1
3

1
4

1
5

1
6

1
7

1
8

1
9

2
0

2
1

2
2

2
3

S

0
0

0
1

0
2

0
3

0
4

0
5

0
6

0
7

0
8

0
9

1
0

1
1

1
2

1
3

1
4

1
5

1
6

1
7

1
8

1
9

2
0

2
1

2
2

2
3

S

0
0

0
1

0
2

0
3

0
4

0
5

0
6

0
7

0
8

0
9

1
0

1
1

1
2

1
3

1
4

1
5

1
6

1
7

1
8

1
9

2
0

2
1

2
2

2
3

22

Backups

Transaction Log Backup
M

0
0

0
1

0
2

0
3

0
4

0
5

0
6

0
7

0
8

0
9

1
0

1
1

1
2

1
3

1
4

1
5

1
6

1
7

1
8

1
9

2
0

2
1

2
2

2
3

T

0
0

0
1

0
2

0
3

0
4

0
5

0
6

0
7

0
8

0
9

1
0

1
1

1
2

1
3

1
4

1
5

1
6

1
7

1
8

1
9

2
0

2
1

2
2

2
3

W

0
0

0
1

0
2

0
3

0
4

0
5

0
6

0
7

0
8

0
9

1
0

1
1

1
2

1
3

1
4

1
5

1
6

1
7

1
8

1
9

2
0

2
1

2
2

2
3

T

0
0

0
1

0
2

0
3

0
4

0
5

0
6

0
7

0
8

0
9

1
0

1
1

1
2

1
3

1
4

1
5

1
6

1
7

1
8

1
9

2
0

2
1

2
2

2
3

F

0
0

0
1

0
2

0
3

0
4

0
5

0
6

0
7

0
8

0
9

1
0

1
1

1
2

1
3

1
4

1
5

1
6

1
7

1
8

1
9

2
0

2
1

2
2

2
3

S

0
0

0
1

0
2

0
3

0
4

0
5

0
6

0
7

0
8

0
9

1
0

1
1

1
2

1
3

1
4

1
5

1
6

1
7

1
8

1
9

2
0

2
1

2
2

2
3

S

0
0

0
1

0
2

0
3

0
4

0
5

0
6

0
7

0
8

0
9

1
0

1
1

1
2

1
3

1
4

1
5

1
6

1
7

1
8

1
9

2
0

2
1

2
2

2
3

23

Backups

Figured out it��s
safer to have a
��Restore
Strategy�� yet?
24

Backups

Optimizing backups

Backup
Options
Database
Architecture
Server
Architecture

25

Backups

Have a
��Restore
Strategy��
26

Backups

Optimizing backups �C Server Architecture
? IO Subsystem

The DBA team start negotiations
with the Infrastructure team

�C RAID Levels
�C Disk Partition Alignment
�C Dedicated drives

? Network cards
�C Add more NICs
�C Use different subnets
�C Use different switches

http://www.flickr.com/photos/jakesutton/859193891/in/photostream

27

Backups

Have a
��Restore
Strategy��
28

Backups

Optimizing backups �C Database Architecture
? Table design
�C See Natural Born Killers webcast November 2012 (http://dell.to/15RwEtz)
�C Blogged at http://bit.ly/GHuUrH

? Index Design
�C See indexing webcasts October 2012 (http://dell.to/19urm8s)

? File placement

29

Backups

End of
subliminal
messages
30

Backups

Backup Options
Optimizing Backups

Other Backup Options of note

? Compression

?

Init / NoInit

? BufferCount

?

Format / NoFormat

? MaxTransferSize

?

Copy Only

? BlockSize

?

Checksum

? Multiple backup devices

?

Continue After Error

For a full list of all the backup options refer to Books Online (BOL)
http://msdn.microsoft.com/en-us/library/ms186865.aspx
31

Backups

Demo
32

Backups

Global Marketing

Restore Myths
?
?

No need to test backups

?

Restore time will be the same as the backup time

?

34

Downgrading a backup

Table restores

Backups

Optimizing restores
? Using ��Instant File Initialization��
? Using the correct restore strategy

? Server Architecture
? Database Architecture
? Restore options
�C Buffercount
�C MaxTransferSize

35

Backups

Any questions?

36

Backups

Global Marketing

Thank you for attending
Richard Douglas
Richard.Douglas@Software.Dell.com
@SQLRich

�ݺ�ߣ

I got 99 Problems but my backup ain't one by Richard Douglas

More Related Content

I got 99 Problems but my backup ain't one by Richard Douglas

Editor's Notes