Module 5: Restoring SQL Server 2014 Databases

1 of 41

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

Module Contents:

Lesson 1: Lesson 2: Lesson 3: Lesson 4: Lab:

Module In the previous databases. A

Server® 2014 is essential

that you can You will often

ensure that

you have a clear

the required

state. A good

making the

situation worse. Some database

to return the

system as close

though, are

related to human

the error.

The point-in-time

this.

26/09/2016 15:45

Module 5: Restoring SQL Server 2014 Databases

2 of 41

User databases because they and special care understand how

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

databases affected by failures, to same process for

all system databases. In this module,

implement

point-in-time

Objectives After completing • Explain the • Restore databases. • Perform advanced • Perform a

Lesson When you need

causing

further damage.

create a

tail-log backup,

restore—and in

which order.

Lesson After completing • Describe the • Describe the • Decide on

26/09/2016 15:45

Module 5: Restoring SQL Server 2014 Databases

3 of 41

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

Phases

Restoring a SQL combination

undo. The recovery of a

database. Data Copy The data copy the database restore process log files. If instant Server service After the data

files from restored, the required data and to the SQL amount of time. database

backup. Data

files. The

log files need

a

substantial time If a differential

in the data

files with those Redo Phase At the start of

log. In the

26/09/2016 15:45

Module 5: Restoring SQL Server 2014 Databases

4 of 41

simple recovery differential backup. supplemented

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

backup or the details are the full and

differential database In the redo phase, details into the

transaction log typically the

latest time for Undo Phase The transaction recovery point,

committed at the Server rolls

back any of these Because the

transactions and

placing the database During the undo online and users

database to come recovery feature.

Queries that

undo phase

is complete.

that users can

access the database In general, you to this is the

one exception the undo

phase is continuing. Recovery does taken offline

database is database will also

occur. The same

Note: mirroring that are

database advanced topics

26/09/2016 15:45

Module 5: Restoring SQL Server 2014 Databases

5 of 41

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

Types of

The restore scenarios

the edition of

SQL Server you Complete Database The most basic database backup.

a full the restore of

the full database In most scenarios In these cases, returns the database

are performed. recovery phase backup

being completed. Complete Database The most common restoring full, recover the database

and involves restore is to the database to

earlier points System Database

26/09/2016 15:45

Module 5: Restoring SQL Server 2014 Databases

6 of 41

Restoring system from occurring. will refuse to

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

further issues SQL Server databases

will be discussed Filegroup or SQL Server files in a database database. The in the simple bulk-logged similar to the

if specific to recover the when operating the process that is module.

Piecemeal A piecemeal

on filegroups,

rather than restoring

must be

restored is the want to prioritize

which you Server 2014,

piecemeal restore Page Restore Another advanced

individual data

page is corrupt,

they execute a

query that tries

restore. If a

user query tries

which

indicates the

access the

page will again

and

bulk-logged Online Restore Online restore for File, Page,

default option available in

the Enterprise

26/09/2016 15:45

Module 5: Restoring SQL Server 2014 Databases

7 of 41

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

Preparations

Before restoring

you are

intending to

tail-log backup,

even when damage

backup is

critical when Identifying The recovery

correct order.

The normal process 1.

Restore

individual files are

damaged 2.

If differential

3.

If transaction last differential of the restore

backup. backups since the at the start not apply to

databases

Discussion:

Restore

26/09/2016 15:45

Module 5: Restoring SQL Server 2014 Databases

8 of 41

The scenario

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

the timing of

a failure. What

Lesson Most restore differential backup

by a will learn

how to restore

Lesson After completing • Restore a • Restore a • Restore a

Restoring

26/09/2016 15:45

Module 5: Restoring SQL Server 2014 Databases

9 of 41

You can restore

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

SSMS) or the

RESTORE DATABASE Restoring a The simplest

backup. If

no subsequent

use the

RECOVERY

process for the

database and

prevent recovery

from occurring

either of these

options, SQL In the following

AW.bak

backup media: Restoring a Database

RESTORE FROM DISK

Replacing SQL Server will

database if you

26/09/2016 15:45

Module 5: Restoring SQL Server 2014 Databases

10 of 41

have not performed SQL Server will for you. If you

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

using SSMS, tail-log backup tail-log backup,

you must specify In the following

replaced with the

database in the

RESTORE FROM DISK WITH REPLACE';

Note: data loss.

lead to

Restoring When you restore

database files

in different locations

You might also

need to do this

WITH MOVE

option enables In this example,

server. As well

as specifying

database file are

also specified

the

specification WITH MOVE

RESTORE FROM DISK WITH MOVE

mdf',

26/09/2016 15:45

Module 5: Restoring SQL Server 2014 Databases

11 of 41

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

AdventureWorks.

Restoring a SQL Server provides recovered, by

been NORECOVERY. After

you restore a

further

transaction log

to support

Log Shipping reapplying the STANDBY option

by can also use the to bring

online.

Restoring

In a differential

and then

restore the latest Database Recovery As discussed

maintenance of

transactional

before the failure

are recorded

back.

26/09/2016 15:45

Module 5: Restoring SQL Server 2014 Databases

12 of 41

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

The RESTORE

WITH

NORECOVERY.

not need to be

specified. This

restored from

a full database

restore additional

backups subsequent

correct option for

each RESTORE

restores must be

performed WITH RECOVERY.

will display

as