Module 6: Backing Up

Databases

Overview

Preventing Data Loss

Setting and Changing a Database Recovery Model

SQL Server Backup

When to Back Up Databases

Performing Backups

Types of Backup Methods

Planning a Backup Strategy

Preventing Data Loss

Have a Backup Strategy

To minimize data loss

To recover lost data

To restore data with minimal cost to production time

Back Up Regularly

Setting and Changing a Database Recovery Model

Setting a Database Recovery Model

Full Recovery model

Bulk_Logged Recovery model

Simple Recovery model

Changing a Database Recovery Model

ALTER
ALTER DATABASE
DATABASE Northwind
Northwind
SET
SET RECOVERY
RECOVERY BULK_LOGGED
BULK_LOGGED

SQL Server Backup

Allows Backups to Occur While Users Continue to

Work with the Database

Backs Up Original Files and Records Their Locations

Captures Database Activities That Occur During the

Backup Process in the Backup

Issues a checkpoint and records the LSN

Writes all pages to the backup media

Writes all transaction log records written during the

backup process

Performing and Storing Backups

Who Performs Backups

Members of the sysadmin fixed server role

Members of the db_owner and db_backupoperator

fixed database roles

Where to Store Backups

Hard disk file

Tape

A location identified by a Named Pipe

When to Back Up Databases

Backing Up System Databases

Backing Up User Databases

Activities That Are Restricted During Backup

Backing Up System Databases

After Modifying the master Database

Using the CREATE DATABASE, ALTER DATABASE,

or DROP DATABASE statement

Executing certain system stored procedures

After Modifying the msdb Database

After Modifying the model Database

Backing Up User Databases

After Creating Databases

After Creating Indexes

After Clearing the Transaction Log

After Performing Nonlogged Operations

BACKUP LOG WITH TRUNCATE_ONLY or

NO_LOG statement

WRITETEXT or UPDATETEXT statement

SELECT...INTO statement

Activities That Are Restricted During Backup

Creating or Modifying Databases

Performing Autogrow Operations

Creating Indexes

Performing Nonlogged Operations

Shrinking a Database

Performing Backups

Creating Backup Devices

Creating Backup Files Without Permanent Devices

Using Multiple Backup Files to Store Backups

Using the BACKUP Statement

Backing Up to a Tape Device

Creating Backup Devices

Why to Create Permanent Backup Devices

To reuse the backup files for future backups

To automate the task of backing up

Using the sp_addumpdevice System

Stored Procedure

Specify the logical name

Logical and physical names are stored in the

sysdevices system table

USE
USE master
master
EXEC
EXEC sp_addumpdevice
sp_addumpdevice 'disk',
'disk', 'mybackupfile',
'mybackupfile',
'C:\Backup\MyBackupFile.bak'
'C:\Backup\MyBackupFile.bak'

Creating Backup Files Without Permanent Devices

Why to Create Backup Files Without Permanent Devices

To perform a one-time-only backup

To test the backup operation that you plan to automate

Using the BACKUP DATABASE Statement

Specify a media type (disk, tape, or Named Pipe)

Specify the complete path and file name

USE
USE master
master
BACKUP
BACKUP DATABASE
DATABASE Northwind
Northwind
TO
TO DISK
DISK == 'C:\Temp\Mycustomers.bak'
'C:\Temp\Mycustomers.bak'

Using Multiple Backup Files to Store Backups

Database B

Media Set
Backup Set

File 1

File 2

File 3

BackupA1
BackupA2
BackupA3
BackupB1

BackupA1
BackupA2
BackupA3
BackupB1

BackupA1
BackupB1
BackupA2
BackupA3
BackupB1

BBaack
B
ckuu p
B aack
ckuup
pBB1
p DDat
1
ataa ba
basse
e

Database A

Using the BACKUP Statement

Specifying the INIT or NOINIT Option

NOINIT option appends to a backup file

INIT option overwrites a backup file

Using the FORMAT Option

Overwrites the contents of a backup file

Splits up a striped backup set

Backing Up to a Tape Device

Requires Tape to Be Attached Locally to SQL Server

Records Backup Information on Tape Label

Stores SQL Server and Non-SQL Server Backups

Specifying Tape Options

Tape
Tapeoption
option

Description
Description

UNLOAD
UNLOAD(default)
(default)

Rewinds
Rewindsand
andunloads
unloadsthe
thetape
tape

NOUNLOAD
NOUNLOAD

Does
Doesnot
notrewind
rewindand
andunload
unloadthe
thetape
tape

BLOCKSIZE
BLOCKSIZE

Changes
Changesthe
thephysical
physicalblock
blocksize
sizein
inbytes
bytes

FORMAT
FORMAT

Writes
Writesaaheader
headeron
onfiles
filesthat
thatare
areused
usedfor
foraabackup
backup

SKIP
SKIP

Ignores
IgnoresANSI
ANSItape
tapelabels
labels

NOSKIP
NOSKIP(default)
(default)

Reads
ReadsANSI
ANSItape
tapelabels
labels

RESTART
RESTART

Restarts
Restartsthe
thebackup
backupoperation
operationfrom
fromthe
thepoint
pointof
of
interruption
interruption

Types of Backup Methods

Performing a Full Database Backup

Performing a Differential Backup

Performing a Transaction Log Backup

Performing a Database File or Filegroup Backup

Performing a Full Database Backup

Provides a Baseline

Backs Up Original Files, Objects, and Data

Backs Up Portions of the Transaction Log

USE
USE master
master
EXEC
EXEC sp_addumpdevice
sp_addumpdevice 'disk',
'disk', 'NwindBac',
'NwindBac',
'D:\MyBackupDir\NwindBac.bak'
'D:\MyBackupDir\NwindBac.bak'
BACKUP
BACKUP DATABASE
DATABASE Northwind
Northwind TO
TO NwindBac
NwindBac
D:\

Northwind

Data

Log

Backup
Backup

NwindBac

Performing a Differential Backup

Use on Frequently Modified Databases

Requires a Full Database Backup

Backs Up Database Changes Since the Last Full

Database Backup

Saves Time in Both Backup and Restore Process

BACKUP
BACKUP DATABASE
DATABASE Northwind
Northwind
DISK
DISK == 'D:\MyData\MyDiffBackup.bak'
'D:\MyData\MyDiffBackup.bak'
WITH
WITH DIFFERENTIAL
DIFFERENTIAL

Performing a Transaction Log Backup

Requires a Full Database Backup

Backs Up All Database Changes from the Last

BACKUP LOG Statement to the End of the Current
Transaction Log

Truncates the Transaction Log

USE
USE master
master
EXEC
EXEC sp_addumpdevice
sp_addumpdevice 'disk',
'disk', NwindBacLog',
NwindBacLog',
'D:\Backup\NwindBacLog.bak'
'D:\Backup\NwindBacLog.bak'
BACKUP
BACKUP LOG
LOG Northwind
Northwind TO
TO NwindBacLog
NwindBacLog

Using the NO_TRUNCATE Option

SQL Server:

Saves the Entire Transaction Log Even if the

Database Is Inaccessible

Does Not Purge the Transaction Log of

Committed Transactions

Allows Data to Be Recovered Up to the Time

When the System Failed

Clearing the Transaction Log

Use the BACKUP Statement to Clear the Transaction

Log

Using the TRUNCATE_ONLY or NO_LOG Option

Cannot recover changes

Is not recorded

Setting the trunc. log on chkpt. Option

Writes all committed transactions

Occurs automatically when set to true

Performing a Database File or Filegroup Backup

Use on Very Large Databases

Back Up the Database Files Individually

Ensure That All Database Files in Filegroup

Are Backed Up

Back Up Transaction Logs

BACKUP
BACKUP
FILE
FILE ==
BACKUP
BACKUP

DATABASE
DATABASE Phoneorders
Phoneorders
Orders2
Orders2 TO
TO OrderBackup2
OrderBackup2
LOG
LOG PhoneOrders
PhoneOrders to
to OrderLog
OrderLog

Restrictions on Backing Up Database Files or

Filegroups
Scenario 1
D:\
Table
Table

Index
Index

Filegroup1

Both files must be

backed up as a unit

Scenario 2
D:\
Table
Table

Filegroup 1
Index
Index 11

Filegroup 2

Index
Index 22

Filegroup 3

Filegroups 1, 2,
and 3 must be
backed up as a unit

Demonstration: Using SQL Server Enterprise

Manager to Perform Backups

Planning a Backup Strategy

Full Database Backup Strategy

Full Database and Transaction Log Backup Strategy

Differential Backup Strategy

Database File or Filegroup Backup Strategy

Full Database Backup Strategy

Created Database
and Performed Full
Database Backup

Data

Log

Sunday

Full Database Backup

Data

Log

Monday

Full Database Backup

Data

Log

Tuesday

Full Database and Transaction Log Backup Strategy

Full Database
Backup

Data

Log
Log

Sunday

Full Database
Backup

Log

Log

Log

Monday

Data

Log

Differential Backup Strategy

Full Database
Backup

Data

Log

Differential
Backup
Log

Log

Monday

Log

Data

Log

Differential
Backup
Log

Log

Log

Tuesday

...
...

Database File or Filegroup Backup Strategy

Full Database
Backup

Data

Log

Log

Monday

Log

Data
File 1

Log

Tuesday

Log

Data
File 2

Log

Log

Wednesday

Data
File 3

Log

Thursday

Log

Performance Considerations

Back Up to Multiple Physical Devices

Type of Physical Backup Device Determines Speed

of Backup Process

Minimize Concurrent Activity on SQL Server

Recommended Practices
Have
Have aa Backup
Backup Strategy
Strategy
Back
Back Up
Up System
System Databases
Databases After
After They
They Have
Have Been
Been Modified
Modified
Schedule
Schedule Backup
Backup Operations
Operations When
When Database
Database Activity
Activity Is
Is Low
Low
Create
Create Backup
Backup Devices
Devices
Test
Test Your
Your Backup
Backup Strategy
Strategy

Lab A: Backing Up Databases

Review

Preventing Data Loss

Setting and Changing a Database Recovery Model

SQL Server Backup

When to Back Up Databases

Performing Backups

Types of Backup Methods

Planning a Backup Strategy