2008 Sql Server Chapter 9

Front 1

Because the backup process is not concerned with the ordering of pages, _______ can be used
to write pages to your backup device.

Back 1

multiple threads

Front 2

The only operations that are not allowed during a full backup are:

Back 2

- Adding or removing a database fi
- Shrinking a database

Front 3

The only two parameters required for a backup are the _________ and the ________.

Back 3

- name of the database
- backup device

Front 4

When you specify more than one backup device, SQL Server _________ the backup across each of the devices specified. You can have up to _______ of these devices.

Back 4

stripes
64

Front 5

The MIRROR TO clause provides a built-in capability to create up to _______copies of a backup in a single operation

Back 5

four

Front 6

You can’t back up to tape and mirror to _______ or vice versa

Back 6

disk,

Front 7

The overhead of compression is always worth it. The amount of time saved for a ______ backup far exceeds the overhead associated with the _________operation.

Back 7

compressed
compression

Front 8

When you specify ________ and you are backing up to a file that already exists, SQL Server appends the new backup to the end of the file. If you specify ______and the file already exists, SQL Server overwrites the file with the contents of the new backup

Back 8

NOINIT

INIT

Front 9

It is recommended that you specify the ______ option to catch bad pages as early as possible.

Back 9

CHECKSUM

Front 10

Essentially, an ____ provides a sequence number for every change made to a database

Back 10

LSN

Front 11

As soon as SQL Server reaches the fi rst LSN that has not yet been ________ (that is, the oldest open transaction), the transaction log backup completes

Back 11

committed

Front 12

A _______ backup captures all extents that have changed since the last full backup.

Back 12

differential

Front 13

The primary purpose of a differential backup is to reduce the number of __________ backups
that need to be restored

Back 13

transaction log

Front 14

SQL Server tracks each extent that has been changed following a full backup using a special page in the header of a database called the _________.

Back 14

Differential Change Map (DCM)

Front 15

A ______backup zeroes out the contents of the DCM.

Back 15

full

Front 16

A transaction log backup is an incremental backup because it captures any changes that have occurred since the last _______backup.

Back 16

transaction log

Front 17

The ___________ option allows you to create a backup that can be used to create the development or test environment, but it does not affect the database state or set of backups in production.

Back 17

COPY_ONLY

Front 18

A full backup with the COPY_ONLY option does not reset the ______ page and therefore has no impact on differential backups.

Back 18

differential change map

Front 19

A transaction log backup with the COPY_ONLY option does not remove ______ from the transaction log.

Back 19

transactions

Front 20

When a partial backup is executed, SQL Server backs up the ________ filegroup, all ____ filegroups, and any explicitly specified _____ filegroups.

Back 20

primary
read/write
read-only

Front 21

To reduce the size of a backup to only the filegroups that can change, you can perform a _______backup.

Back 21

partial

Front 22

When you enable page verification, each time a page is read from disk, SQL Server computes a new _____ and compares it to the checksum stored on the _____. If the checksums do not match, SQL Server returns an error and logs the page into a table in the ____database.

Back 22

checksum
page
msdb

Front 23

Tasks that are supported by maintenance plans are:

Back 23

- backup up dbs
- shrinking dbs
- re-indexing
- updating stats
- consistency checks

Front 24

Maintenance plans are based upon the tasks within _________.

Back 24

SQL Server Integration Services (SSIS)

Front 25

A service master key is created automatically the first time that an ______ is started.

Back 25

instance

Front 26

The _______is regenerated each time that you change the SQL Server service account or service account password.

Back 26

service master key

Front 27

By default, a DMK is encrypted with the _________.

Back 27

service master key

Front 28

If someone were to steal the backup of a database that contained encrypted data, he or she would not be able to access the encrypted data without also having access to the _____ and ____ .

Back 28

master keys
certificates

Front 29

To validate a backup, execute the following command:

Back 29

RESTORE VERIFYONLY FROM <backup device>

Front 30

When a backup is validated, SQL Server performs the following checks:

Back 30

- Calculates a checksum
- Verifies that the header of the backup
- Transits the page chain to ensure that all pages are contained in the database

Front 31

What are the four types of backups?

Back 31

full, differential, transaction log, and file/filegroup backups

Front 32

How can you detect and log corrupt pages?

Back 32

ALTER DATABASE <database name> SET PAGE_VERIFY CHECKSUM

Front 33

A _______backup contains only the pages within the database that have been used.

Back 33

full

Front 34

________ backups contain all pages that have changed since the last full backup and are used to reduce the number of transaction log backups that need to be applied.

Back 34

Differential

Front 35

Transaction log backups contain all the changes that have occurred since the last ___________ backup.

Back 35

transaction log

Front 36

At a database level, the LSN of the last change in the database is stored in the header of the _________

Back 36

master data file

Front 37

At a data file level, the LSN of the last change to a page
within the file is stored in the header of the ________. Each data page within a database also records the LSN of the last change for the _________.

Back 37

data file
data page

Front 38

During the checkpoint process, SQL Server compares the LSN of the ______in the buffer pool to the LSN of the _______ on disk

Back 38

dirty page
data page

Front 39

During the REDO phase, all committed transactions in the transaction log are ________.

Back 39

flushed to disk

Front 40

The UNDO phase moves through the transaction log and invalidates any transaction in the log that is still open, ensuring that an _______ transaction cannot be written out to disk.

Back 40

uncommitted

Front 41

When a RESTORE is issued with the _______ option, the restore completes, but the database is left in a
RECOVERING state such that subsequent differential and/or transaction log backups can be applied.

Back 41

NORECOVERY

Front 42

The ________ option can be used to allow you to issue SELECT statements against the database while still issuing additional differential and/or transaction log restores

Back 42

STANDBY

Front 43

The ______option provides the ability to change the location of one or more data fi les when the database is restored.

Back 43

MOVE

Front 44

The step in the restore process where you first take a final transaction log backup is referred to as backing up the _______.

Back 44

tail of the log

Front 45

To provide the greatest flexibility for a restore, the first step in any restore operation is to issue a _______backup against the original database

Back 45

transaction log

Front 46

The ______ and ______ options allow you to specify either an LSN or a transaction log MARK to use for the stopping point in the restore operation.

Back 46

STOPATMARK

STOPBEFOREMARK

Front 47

The state of the database equals the state of the filegroup designated as ________.

Back 47

PRIMARY

Front 48

Page corruption usually occurs where a ______ begins to fail.

Back 48

disk controller

Front 49

Page restore has several requirements:

Back 49

- must be in either the Full or Bulked-logged recovery model
- must be able to create a transaction log backup
- can apply only to a read/write filegroup.
- must have a valid full, file, or filegroup backup
- page restore cannot be executed at the same time as any other restore operation.

Front 50

procedure to restore a corrupt page

Back 50

1. retrieve PageID of damaged page
2. Restore database page from full backup with NORECOVERY
3. Restore differential backups with NORECOVERY
4. Restore transaction log backups with NORECOVERY
5. Create a transaction log backup
6. Restore transaction log backup from step #5 with RECOVERY

Front 51

To restore from backup media that has been damaged, you need to specify the _______ option for a RESTORE DATABASE or RESTORE LOG command

Back 51

CONTINUE_AFTER_ERROR

Front 52

Which recovery model always allows you to restore to the point of failure so long as you can back up the tail of the log?

Back 52

The Full recovery model.

Front 53

What is the first operation that should be performed for any restore operation?

Back 53

Back up the tail of the log.

Front 54

If you create a Database Snapshot against a database with FILESTREAM data, the FILESTREAM filegroup is ______.

Back 54

disabled

Front 55

Which two features are incompatible with Database Snapshots?

Back 55

FILESTREAM and full text indexes

Front 56

Prior to reverting a database using a Database Snapshot, what must you do?

Back 56

You must drop all Database Snapshots except the Database Snapshot being used as the source for the RESTORE command

Front 57

A Database _______ is a point-in-time, read-only, copy of a database.

Back 57

Snapshot

Front 58

You can revert a database from a Database _______.

Back 58

Snapshot