SQL Server 2005 Backing Up & Restoring Databases

SQL Server 2005

Institute of Informatics,

Silesian University of Technology, Gliwice, Poland

SQL Server 2005

Backing Up & Restoring Databases

Dariusz Mrozek, PhD

Part 1: Backing Up Overview

• Setting and Changing a Database Recovery Model

• SQL Server Backup

2

• When to Back Up Databases • Performing Backups

• Types of Backup Methods • Planning a Backup Strategy

Preventing Data Loss

– To minimize data loss – To recover lost data

– To restore data with minimal cost to production time

• Back Up Regularly

3

Ways we can lose data

You can lose data as a result of hardware or software failures or:

• Accidental or malicious use of the DELETE statement. • Accidental or malicious use of the UPDATE statement —

for example, not using a WHERE clause with the

UPDATE statement (all rows are updated rather than a

4

UPDATE statement (all rows are updated rather than a single row in a particular table).

• Destructive viruses.

• Natural disasters, such as fire, flood, and earthquakes. • Theft.

“How much am I willing to

pay, and how much loss is

acceptable to me?”

Back Up Regularly

• You might back up your database frequently if your system is in an online transaction processing (OLTP) environment.

• You might back up your database less frequently if your system has little activity or is used primarily for decision support.

5

support.

• You should schedule backups when SQL Server is not in the process of being heavily updated.

• After you determine your backup strategy, you can automate the process by using the SQL Server Agent.

Setting and Changing a Database

Recovery Model

• Setting a Database Recovery Model

– Full Recovery model

• uses copies of the database and all log information to restore the database

• recover all data except transactions actually in process at the time of the failure

– Bulk_Logged Recovery model

• similar to Full

• uses less log space for some operations

6

ALTER DATABASE AdventureWorks SET RECOVERY BULK_LOGGED

• uses less log space for some operations • can restore all data

– Simple Recovery model

• for small databases or databases where data changes infrequently

• recovery is limited to restoring the database to the point when the last backup was made

• takes less storage space for logs and is the simplest model to implement

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

7

• 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

– Members of the sysadmin fixed server role – Members of the db_owner and

db_backupoperator fixed database roles

• Where to Store Backups

8

• Where to Store Backups

– Hard disk file – Tape

When to Back Up Databases

• Backing Up User Databases

• Activities That Are Restricted During Backup

Backing Up System Databases

– Using the CREATE DATABASE, ALTER DATABASE, or DROP DATABASE statement

– Executing certain system stored procedures, like: sp_addserver, sp_dropserver,

10

sp_addserver, sp_dropserver,

sp_addlinkedserver, sp_addmessage,

• After Modifying the msdb Database • After Modifying the model Database

Backing Up User Databases

• After Creating Indexes

• After Clearing the Transaction Log

• After Performing Nonlogged Operations

11

• 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

12

Performing Backups

• Creating Backup Files Without Permanent Devices

• Using Multiple Backup Files to Store Backups • Using the BACKUP Statement

13

• Using the BACKUP Statement • Backing Up to a Tape Device

Creating Backup Devices

Devices

– To reuse the backup files for future backups – To automate the task of backing up

• Using the sp_addumpdevice System

14

• Using the sp_addumpdevice System Stored Procedure

– Specify the logical name

– Logical and physical names are stored in the sys.backup_devices system table

USE master GO

EXEC sp_addumpdevice 'disk', 'AWBackupDevice', 'C:\Backup\AWbackup.bak' GO

Creating Backup Devices

USE master; GO

sp_addumpdevice [ @devtype = ] 'device_type' , [ @logicalname = ] 'logical_name' , [ @physicalname = ] 'physical_name‘

15 USE master;

GO

EXEC sp_addumpdevice 'disk', 'networkdevice',

'\\<servername>\<sharename>\<path>\<filename>.bak';

USE master; GO

Creating Backup Devices

Backuping Database

BACKUP DATABASE { database_name | @database_name_var }

TO < backup_device > [ ,...n ]

[ [ MIRROR TO < backup_device > [ ,...n ] ] [ ...next-mirror ] ] [ WITH

[ BLOCKSIZE = { blocksize | @blocksize_variable } ] [ [ , ] { CHECKSUM | NO_CHECKSUM } ]

[ [ , ] { STOP_ON_ERROR | CONTINUE_AFTER_ERROR } ] [ [ , ] DESCRIPTION = { 'text' | @text_variable } ] [ [ , ] DIFFERENTIAL ]

[ [ , ] EXPIREDATE = { date | @date_var }

17

[ [ , ] EXPIREDATE = { date | @date_var } | RETAINDAYS = { days | @days_var } ]

[ [ , ] PASSWORD = { password | @password_variable } ] [ [ , ] { FORMAT | NOFORMAT } ]

[ [ , ] { INIT | NOINIT } ] [ [ , ] { NOSKIP | SKIP } ]

[ [ , ] MEDIADESCRIPTION = { 'text' | @text_variable } ] [ [ , ] MEDIANAME = { media_name | @media_name_variable } ]

[ [ , ] MEDIAPASSWORD = { mediapassword | @mediapassword_variable } ] [ [ , ] NAME = { backup_set_name | @backup_set_name_var } ]

[ [ , ] { NOREWIND | REWIND } ] [ [ , ] { NOUNLOAD | UNLOAD } ] [ [ , ] RESTART ] [ [ , ] STATS [ = percentage ] ] [ [ , ] COPY_ONLY ] ]

Backuping Database

USE master GO

BACKUP DATABASE AdventureWorks TO AWBackupDev GO

USE master

18 USE master

GO

RESTORE HEADERONLY FROM AWBackupDev GO

Backuping Database

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

20

USE master

BACKUP DATABASE AdventureWorks

TO DISK = 'C:\AdventureWorks.bak'

automate

• Using the BACKUP DATABASE Statement

– Specify a media type (disk, tape)

Storing Backups on Multiple Backup Files

• A backup set is a result of a single backup operation on single or multiple files.

• A media set is an ordered collection of backup media, tapes or disk files, to which one or more backup operations have written using a fixed

21

backup operations have written using a fixed type and number of backup devices.

• Backuping to multiple tapes or disk controllers decreases the total time that it takes to back up a database.

BACKUP DATABASE {database_name | @database_name_var} TO <backup_device> [, ...n]

[WITH

[MEDIANAME = {media_name | @medianame_var}] ]

Using Multiple Backup Files

to Store Backups

Database A

Database A Database BDatabase B

Database A

Database A Database BDatabase B

22 Media Set File 1 BackupA1 BackupA2 BackupA3 File 2 BackupA1 BackupA2 BackupA3 File 3 BackupA2 BackupA3

Backup Set BackupA1BackupB1 Media Set File 1 BackupA1 BackupA2 BackupA3 File 2 BackupA1 BackupA2 BackupA3 File 3 BackupA2 BackupA3

Backup Set BackupA1

Storing Backups on Multiple Backup Files

• All devices that are used in a single backup operation must be of the same media type (disk or tape). You cannot mix disk and tape

devices for a single backup media set.

• You can use a combination of permanent and temporary files when you create a backup set.

• If you define a file as a member of a backup set, you must always use the files together.

23

use the files together.

• You cannot use only one member of the backup set for a backup operation unless you reformat the files.

• If you reformat one member of a backup set, the data that is contained in the other members of the backup set is invalid and unusable.

AdventureWorks2b.bak', BACKUP DATABASE AdventureWorks

TO DISK='C:\AdventureWorks1a.bak', DISK='C:\AdventureWorks2a.bak', DISK='C:\AdventureWorks3a.bak'

MIRROR TO DISK='C:\AdventureWorks1b.bak', DISK='C:\AdventureWorks2b.bak', DISK='C:\AdventureWorks3b.bak'

Using the BACKUP Statement

– NOINIT option appends to a backup file – INIT option overwrites a backup file

• Using the FORMAT Option

– Overwrites the contents of a backup file

24

– Overwrites the contents of a backup file – Splits up a striped backup set

USE master GO

BACKUP DATABASE AdventureWorks TO DISK='C:\AdventureWorks1.bak‘ WITH INIT, STATS=10

Backing Up to a Tape Device

• Requires Tape to Be Attached Locally to SQL Server • Records Backup Information on Tape Label

– Database name – Time – Date – Type of backup 25 – Type of backup

• Stores SQL Server and Non-SQL Server Backups (e.g. Microsoft Windows® XP backups)

USE master GO

BACKUP DATABASE AdventureWorks TO TAPE=‘\\.\tape0‘

Specifying Tape Options

Tape option

Tape option DescriptionDescription

UNLOAD (default) Rewinds and unloads the tape

NOUNLOAD Does not rewind and unload the tape

26

BLOCKSIZE Changes the physical block size in bytes

FORMAT Writes a header on files that are used for a backup SKIP Ignores ANSI tape labels

NOSKIP (default) Reads ANSI tape labels

RESTART Restarts the backup operation from the point of interruption

Types of Backup Methods

• 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

– Backs up any activity that took place during the backup.

– Backs up any uncommitted transactions in the transaction log.

28 D:\ AWBacDev Backup Backup Data Log AdventureWorks USE master

EXEC sp_addumpdevice 'disk', ‘AWBacDev', 'D:\MyBackupDir\AWBackup.bak'

Performing a Differential Backup

• Requires a Full Database Backup

• Backs Up Database Changes Since the Last Full Database Backup

• Saves Time in Both Backup and Restore

29

• Saves Time in Both Backup and Restore Process

BACKUP DATABASE AdventureWorks

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

Performing a Transaction Log Backup

• Backs Up All Database Changes from the Last BACKUP LOG Statement to the End of the

Current Transaction Log

• Truncates the Transaction Log

30

USE master

EXEC sp_addumpdevice 'disk', ‘AWBacDevLog', 'D:\Backup\AWBackupLog.bak'

BACKUP LOG AdventureWorks TO AWBacDevLog • Truncates the Transaction Log

• NOTE: You cannot back up transaction logs when using the Simple Recovery model.

Using the NO_TRUNCATE Option

• Saves the Entire Transaction Log Even if the Database Is Inaccessible

• Does Not Purge the Transaction Log of

31

Committed Transactions

• Allows Data to Be Recovered Up to the Time When the System Failed

USE master

EXEC sp_addumpdevice 'disk', ‘AWBacDevLog', 'D:\Backup\AWBackupLog.bak'

BACKUP LOG AdventureWorks TO AWBacDevLog WITH NO_TRUNCATE

Clearing the Transaction Log

• Use the BACKUP Statement to Clear the Transaction Log

• Using the TRUNCATE_ONLY or NO_LOG Option

32

– Cannot recover changes – use BACKUP DATABASE shortly

– Is not recorded

USE master GO

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

33

• Back Up Transaction Logs

BACKUP DATABASE AdventureWorks FILE = AWorks2 TO AWorksBackup2 GO

BACKUP LOG AdventureWorks to AWorksBackLog GO

Restrictions on Backing Up Database

Files or Filegroups

D:\

Both files must be backed up as a unit Scenario 1 Table Table Table Table Filegroup1 Filegroup1 Index Index Index Index 34 D:\ backed up as a unit Scenario 2 Filegroup 2 Filegroup 2 Index 1 Index 1 Index 1 Index 1 Filegroup 3 Filegroup 3 Index 2 Index 2 Index 2 Index 2 Filegroup 1 Filegroup 1 Table Table Table Table Filegroups 1, 2, and 3 must be backed up as a unit

Planning a Backup Strategy

• Full Database and Transaction Log Backup Strategy

• Differential Backup Strategy

• Database File or Filegroup Backup Strategy

35

Full Database Backup Strategy

and Performed Full Database Backup

Full Database Backup Full Database Backup

36

Sunday Monday Tuesday

Data Log Data Log Data Log

Full Database and Transaction Log

Backup Strategy

Log Log Log

Log _Data Log

Full Database Backup Differential Backup Differential Backup

Differential Backup Strategy

38 Monday Tuesday ... ... ... ... Log Data Log

Database File or Filegroup Backup

Strategy

Full Database Backup

39

Monday Tuesday Wednesday Thursday

Data File 1 Data File 3 Data File 2 Log Data Log

Performance Considerations

• Type of Physical Backup Device Determines Speed if Backup Process

• Minimize Concurrent Activity on SQL Server

Recommended Practices

Have a Backup Strategy

Back Up System Databases After They Have

Been Modified

Schedule Backup Operations When Database

Activity Is Low

41

Activity Is Low

Create Backup Devices

Backup Review

• Setting and Changing a Database Recovery Model

• SQL Server Backup

• When to Back Up Databases

42

• When to Back Up Databases • Performing Backups

• Types of Backup Methods • Planning a Backup Strategy

Part 2: Restoring Overview

• Preparing to Restore a Database • Restoring Backups

• Restoring Databases from Different Backup Types

43

Types

SQL Server Recovery Process

BEGIN COMMIT COMMIT

Committed transactions are rolled forward and written to the database

Uncommitted transactions are rolled back and are not written to the database

SQL Server Activities

During the Restore Process

– Database already exists – Database files are different – Database files are incomplete

• Recreates the Database and All Associated

45

• Recreates the Database and All Associated Files

Preparing to Restore a Database

• Performing Tasks Before Restoring Backups

Verifying Backups

• RESTORE HEADERONLY Statement

– Returns header information of a backup file or backup set

• RESTORE FILELISTONLY Statement

– Returns information about the original database or transaction log files

47

• RESTORE LABELONLY Statement

– Returns information about the backup media

• RESTORE VERIFYONLY Statement

– Verifies that individual files are complete and readable

RESTORE HEADERONLY Statement

Information:

• The backup file or backup set name and description

• The type of backup media that was used, such as a tape or hard disk

• The backup method, such as a full database, differential,

48

• The backup method, such as a full database, differential, transaction log, or file backup

• The date and time that the backup was performed • The size of the backup

• The sequence number of a particular backup within a chain of backup files

RESTORE FILELISTONLY Statement

transaction log files

• The physical names of the database and transaction log files

• The type of file, such as a database or a

49

• The type of file, such as a database or a transaction log file

• The filegroup membership

• The backup set size, in megabytes (MB) • The maximum allowed file size, in MB

Verifying Backups

USE master GO

RESTORE HEADERONLY FROM AWBackupDev GO

RESTORE FILELISTONLY FROM AWBackupDev GO

50

GO

RESTORE LABELONLY FROM AWBackupDev GO

RESTORE VERIFYONLY FROM AWBackupDev GO

RESTORE HEADERONLY FROM DISK='c:\AdventureWorks1a.bak‘ GO

RESTORE FILELISTONLY FROM DISK='c:\AdventureWorks1a.bak‘ GO

Performing Tasks

Before Restoring Backups

– Limit access to members of the db_owner, dbcreator, or sysadmin role

• Back Up the Transaction Log

– Ensures database consistency

51

– Ensures database consistency

– Captures changes between the last transaction log backup and when the database was taken offline

Restoring Backups

• Initiating the Recovery Process • Specifying Restore Options

Using the RESTORE Statement

• Restoring Damaged User Databases USE master

RESTORE DATABASE AdventureWorks FROM AWBackupDev

53

– You do not need to drop the damaged database

– SQL Server automatically recreates the database files and objects

Initiating the Recovery Process

– Use with the last backup to be restored – Allows access to database

• Specifying the NORECOVERY Option

– Use with all backup files except for the last

54

– Use with all backup files except for the last backup to be restored

Specifying Restore Options

RESTORE option

RESTORE option DescriptionDescription

FILE

Restores a specific backup You must specify a file number

RESTART Continues an interrupted recovery operation

55

RESTART Continues an interrupted recovery operation

MOVE…TO

Specifies where to restore the backup files

Use to restore to different disk, server, or standby SQL Server

REPLACE

Replaces an existing database

Restoring Databases from Different

Backup Types

• Restoring from a Full Database Backup • Restoring from a Differential Backup

• Restoring a Transaction Log Backup

• Restoring from a File or Filegroup Backup

Restoring from a Full Database Backup

– Physical disk is damaged

– Entire database is damaged, corrupted, or deleted

– To maintain an identical copy of database on another

SQL Server

57

SQL Server

• Specifying Recovery Options

– Initiate the recovery process with the RECOVERY option (no TLB)

– Postpone the recovery process with the NORECOVERY option (next TLB)

USE master

RESTORE DATABASE AdventureWorks FROM AWBackupDev

Restoring from a Differential Backup

• Restores Only the Parts of the Database That Have Changed Since the Last Full Database Backup

• Returns the Database to the Exact State It Was in When the Differential Backup Was Performed

58

USE master

RESTORE DATABASE AdventureWorks FROM AWBackupDiff

WITH NORECOVERY

• Takes Less Time Than Applying a Series of Transaction Logs

Specify the

backup file that contains the

differential backup

Syntax is the same as when you restore a full database

Restoring a Transaction Log Backup

∆∆∆∆

AdventureWorks Database Backups

Full Database Differential Differential

∆

Database Damaged

Log Data Log

Log Log

∆∆∆∆

∆∆∆∆

59

Restore AdventureWorks Database

Full Database Differential

Log Log

Data Log

∆∆∆∆

USE master

RESTORE LOG AdventureWorks FROM AWBackupLog

WITH FILE = 2, RECOVERY Database Damaged

Database Damaged Database Damaged Database Damaged

Specifying a Point in Time

∆∆∆∆

AdventureWorks Database Backups

Full Database Differential Differential

∆

Log Log

∆∆∆∆

∆∆∆∆

USE master

RESTORE LOG AdventureWorks FROM AWBackupLog

WITH FILE = 2, RECOVERY,

60

Restore AdventureWorks Database

Full Database Differential

Data Log

Log Log

∆∆∆∆

Restoring from a File

or Filegroup Backup

• Apply All Transaction Logs Since the File Backup

• Restore Filegroup Backups Containing Indexes and Tables as One Unit

61

USE master

RESTORE DATABASE AdventureWorks FILE = AWFile2

Restoring Damaged System Databases

• Restoring System Databases from a Backup • Rebuilding System Databases

(REBUILDDATABASE option in Setup.exe) • Attaching or Restoring User Databases

62

• Attaching or Restoring User Databases

– Restore from a backup

– Attach with sp_attach_db or

Recommended Practices

Obtain Information About Backups Before You

Restore

Specify NORECOVERY on All Except the Last

Backup

Use RECOVERY on the Last Backup to Return

63

Use RECOVERY on the Last Backup to Return

the Database to a Consistent State

Add a Log Mark Before Performing a High-Risk

Operation

Test Backup Files Periodically By Using RESTORE VERIFYONLY

Restoring Databases Review

• Preparing to Restore a Database • Restoring Backups

• Restoring Databases from Different Backup Types

64

Types

Thank you for your attention!

Questions?