sql server 2008 implementation and maintenance chapter 7: performing backups and restores

SQL Server 2008 Implementation and

Maintenance

Chapter 7: Performing Backups and Restores

Backing Up Data

A backup is a copy of your data stored in another location

There are many reasons to backup• Hardware failures• Natural disaster• Malicious updates

© Wiley Inc. 2006. All Rights Reserved.

Three types of data to backup

DB data Log data FileStream data Important:

• DB data and Log data should not be on the same storage device to start, they should not be backed up to the same backup device.

Recovery Mode

Simple Full Bulk-Logged Yes you can change a DB’s recovery model

How Backups Work

All backups are online backups – meaning the users can use the database while you backing up the database

SQL Server adds an LSN (log sequence number) to each log entry

SQL Server adds a checkpoint every time it writes the log to the database

The current LSN is recorded when the backup starts


How Backups Work, Cont.

The backup process is:• SQL Server checkpoints the data and records

the LSN of the oldest open transaction • SQL Server backs up all the pages of the

database that contain data


Backup Devices

Backups are stored on physical media• Hard disk (local or remote)• Tape drive

Backup devices tell SQL Server about the media• Temporary devices are created on the fly • Permanent devices are for reuse


Full Backups

This is a backup of the entire database, including:• Data files and locations• Portions of the transaction log

You must have a full backup before t-log and differential backups• That makes this a baseline backup


Differential Backups

This records all of the changes since the last full backup

To find changes SQL Server• Reads the last LSN of the last full backup• Compares it with data pages• Backs up the entire extent when it finds a

changed page


Transaction Log Backups

Records only sections of the transaction log

This is the only backup that will clear the transaction log

If you don’t back up the log, it will fill up and users will be locked out


Filegroup Backups

VLDBs can be broken up into filegroups Each filegroup can be backed up

separately, except• Tables and indexes must be backed up

together if they are on separate filegroups


Other backup method Partial

• Skip the read-only filegroups

Differential Partial• Differential + Partial

Copy-Only• Not affect the backing up sequence (no new

LSN number issues) – for making a copy of production database

Mirrored

Using Multiple Devices

Multiple devices can speed up backups SQL Server writes data in stripes to

multiple devices in parallel• All devices are written to at once

These devices become a media set Files in a media set can’t be used

individually


Restores

Backups must be restored in order• Full, then differentials, then t-logs

On the last restore, use the RECOVERY option• This tells SQL Server to let users back into

the database


Restore Safety Checks

SQL Server records the original file locations• Use the MOVE..TO option to override

It also records the original name • Use the REPLACE option to override


Point-in-Time Restores

You can restore a database to a specific point in time

You must have t-log backups in place to do this

Use the STOPAT option to perform this restore


Piecemeal Restores

Use this to restore• The primary filegroup• Optionally, secondary filegroups

This lets you make part of a database available to users


Backup Strategy

You must have a backup strategy in place before disaster strikes

Decide what backup types you will use and when

Decide which databases to backup and when


Full Backups Only

Useful for “relatively small” databases This is the slowest backup strategy It has the fastest restore time Make sure to clear t-logs manually


Full With Differential

Perform a full backup once a week or so Perform differentials other times This is a faster backup strategy It takes longer to restore

• You have to restore more files

Make sure to clear the t-log manually


Full With T-log

Perform a full backup every night Perform t-log backups throughout the day This is a slower backup strategy than

full/differential It takes longer to restore than just full

• You have to restore more files

You get point-in-time restores T-logs are cleared for you


Full/Differential/T-log

This is the fastest backup strategy It is the slowest restore, but only

marginally You get point-in-time restores The t-logs are cleared for you


Filegroup Backups

This is used for VLDBs Perform a full backup once a month or so Backup a filegroup once a week Perform t-log backups throughout the day


Two new backup features for 2008

Compressed backup (4:1)• Slow, not recommended, • Not effective for pictures

Encrypted • Need the key to decrypt • Recommended to consider for the right

application

sql server 2008 implementation and maintenance chapter 7: performing backups and restores

Documents

backup devices backups

data wiley

backups sql server

differential backups

database sql server

baseline backup wiley

backup process

data pages