Administering Sql: Manage Data

Front 1

3 recovery models

Back 1

- Full

- Bulk-logged

- Simple (truncate log on checkpoint)

Front 2

Full recovery

Back 2

•Pointin time recovery

•Failedmedia recovery

Front 3

Bulk-logged recovery

Back 3

•Interimrecovery model used to reduce logging

•Pointin time recovery is not available within bulk options

Front 4

Simple recovery

Back 4

(truncate log on checkpoint)

•Logbackups are not available, no point in time recovery

•Smalldatabases or databases that can be rebuilt from other source data

(Basically enough info to restart database if I need it)

Front 5

2 backup statements

Back 5

–BACKUPDATABASE

–BACKUPLOG

Front 6

TSQL that controls whether the backup operation appends to or overwrites the existing backup sets on the media. What is the default?

Back 6

INIT/NOINIT

default is to append to the most recent backup (NOINIT)

Front 7

Used only with BACKUP DATABASE to indicate a backup that is only since the last full backup

Back 7

WITH DIFFERENTIAL

Front 8

TSQL that specifies whether the media header should be written on the volumes used for this backup operation, overwriting any existing media header and backup sets

Back 8

FORMAT/NOFORMAT

Front 9

What TSL command backs up the tail of the log and leaves the database in the RESTORING state

Back 9

NORECOVERY

(useful when failing over to secondary DB or when saving the tail of the log before a RESTORE operation)

Front 10

4 integrity and fact-finding statements to get information about the backups

Back 10

•RESTORE VERIFYONLY FROM device

•RESTORE HEADERONLY FROMdevice

•RESTORE FILELISTONLY FROM device

•RESTORE LABELONLY FROMdevice

Front 11

Why use system database backups?

Back 11

•Certain system database corruptionscause the failure of the SQL Server or SQL Server Agent Services

Front 12

Specifies that the backup operation will verify each page for checksum and torn page, if enabled and available, and generate a checksum for the entire backup.

Back 12

CHECKSUM

Front 13

Explicitly disables the generation of backup checksums (and the validation of page checksums). This is the default behavior.

Back 13

NO_CHECKSUM

Front 14

TSQL to backup database and log

Back 14

BACKUP DATABASE

BACKUP LOG

Front 15

Only says if the backup was ok or not

Back 15

RESTORE VERIFYONLY

Front 16

can return information like subject line of backup

Back 16

RESTORE HEADERONLY

Front 17

Lists files that were in the backup

Back 17

RESTORE FILELISTONLY

Front 18

Returns a result set containing information about the backup media identified by the given backup device.

Back 18

RESTORE LABELONLY

Front 19

Recovery phases (3)

Back 19

Find ALL backups

uncompleted backups (delete) and completed backups that need to be rolled forward

(–Data copy

–Redo

–Undo)

Front 20

Recovery process

Back 20

–Tail log backup

–Restore full-database backup

–Restore mostrecent differential backup

–Restore alllogs from the most recent differential

–Restore thetail of the log and recover the database

Front 21

List 7 types of recovery

Back 21

•Complete database restore in simplerecovery

•Complete database restore in full recovery

•System database restore

•File restore

•Online restore

•Piecemeal restore

•Page restore

Front 22

2 types of restore database statements

Back 22

RESTORE DATABASE

RESTORE LOG

Front 23

Roll back is controlled by the RESTORE statement rolling forward through this statement. Which is default?

Back 23

RECOVERY (default)

NORECOVERY

Front 24

What TSQL do you use to indicate position in the media set used for backup

Back 24

WITH FILE = [1]

Front 25

What 2 commands can be used to override the backup of the tail log?

Back 25

WITH REPLACE

WITH STOPAT

(Also RESTORE LOG: WITHSTOPATMARK / STOPBEFOREMARK)

Front 26

Moves restored DB (T-SQL)

Back 26

WITH MOVE [location]

Front 27

lets you restore your full backup, then run a few queries before you keep restoring transaction logs.

Back 27

WITH STANDBY

Front 28

What DB will you rarely (if ever) recover?

Back 28

TEMPDB because that is created everytime the instance is started

Front 29

•Recovering model when corrupt

Back 29

SQL Server will not start. Restore model from backup and also user DB

Front 30

Recovering MSDB when corrupt

Back 30

Agent service won't start, so that needs to be restored

Front 31

T-SQL command to move, copy, or rename DB

Back 31

sp_detach_db

then

CREATE Database … FOR ATTACH

Front 32

catalog view to find logical filenames (in order to help with naming other files)

Back 32

sys.database_files

Front 33

What command relocates a DB file within an instance?

Back 33

ALTER DATABASE

(requires use of logical filenames)

Front 34

Where can you change location of master DB files and log file? What parameters are needed?

Back 34

SQL Server Configuration Manager (from start menu)

-d for date file

-l for log file

Then restart

Front 35

How to move other system files (not Master)?

Back 35

ALTER DATABASE....

then move file(s).

Restart SQL Server

Front 36

Database Console Command for informational statements

Back 36

DBCC OPENTRAN

(Tasks that gather and display various types of information.)

Front 37

Database Console Command for validation statements

Back 37

DBCC CHECKDB

(Validation operations on a database, table, index, catalog, filegroup, or allocation of database pages.)

Front 38

Database Console Command for Maintenance statements

Back 38

DBCC FREEPROCCACHE

(Maintenance tasks on a database, index, or filegroup.)

Front 39

Database Console Command for miscellaneous statements

Back 39

DBCC TRACEON/TRACEOFF

(Miscellaneous tasks such as enabling trace flags or removing a DLL from memory.)

Front 40

Info about DBCC CheckDB

Back 40

Validate in DB structure and integrity and the data inside

Front 41

What other commands are spawned by DBCC CheckDB?

Back 41

–DBCC CHECKALLOC–DBCC CHECKTABLE–DBCC CHECKCATALOG

Front 42

Limits the checking to the integrity of the physical structure of the page and record headers and the allocation consistency of the database. This check is designed to provide a small overhead check of the physical consistency of the database, but it can also detect torn pages, checksum failures, and common hardware failures that can compromise a user's data.

Back 42

PHYSICAL_ONLY

Front 43

Displays the estimated amount of tempdb space that is required to run DBCC CHECKDB with all the other specified options. The actual database check is not performed.

Back 43

ESTIMATEONLY

Front 44

Specifies that intensive checks of nonclustered indexes for user tables should not be performed. This decreases the overall execution time.

Back 44

NOINDEX

Front 45

Displays all reported errors per object. All error messages are displayed by default.

Back 45

ALL_ERRORMSGS

Front 46

If the compatibility level is 100 (SQL Server 2008) or higher, performs logical consistency checks on an indexed view, XML indexes, and spatial indexes, where present.

Back 46

EXTENDED_LOGICAL_CHECKS

Front 47

Suppresses all informational messages.

Back 47

NO_INFOMSGS

Front 48

Causes DBCC CHECKDB to obtain locks instead of using an internal database snapshot. will cause DBCC CHECKDB to run faster on a database under heavy load

Back 48

TABLOCK

Front 49

If the compatibility level is 100 (SQL Server 2008) or higher, performs logical consistency checks on an indexed view, XML indexes, and spatial indexes, where present.

Back 49

EXTENDED_LOGICAL_CHECKS

Front 50

REPAIR_REBUILD

Back 50

•Causes no loss of data integrity withinthe database

•Rebuilds indexes and deallocates corruptpages

•Does not involve data loss

•Only works in certain circumstances

Front 51

REPAIR_ALLOW_DATA_LOSS

Back 51

•Repair should absolutely be the lastresort•Database damage is likely

•If the database is in Emergency Repairmode (ALTER DATABASE) special repairs are attempted with this option

Front 52

What indexes is a Primary Key Constraint?

Back 52

Clustered and Non-Clustered Index, Unique

Front 53

What indexes is a Unique Key Constraint?

Back 53

Clustered and Non-Clustered Index, Unique

Front 54

What indexes is a Regular Index?

Back 54

Clustered and Non-Clustered Index, Non-Unique

Clustered and Non-Clustered Index, Unique

Front 55

What is the difference between a clustered and non-clustered index?

Back 55

- Clustered indexes sort and store the data rows in the table or view based on their key values

- Nonclustered indexes have a structure separate from the data rows (using pointers ["row locators"])

Front 56

What are three reasons statistics can help in determining an optimal execution plan?

Back 56

–May aid in index selection and query processing

–Join type (nested loop, hash)

–Join order (table 1, table 2, or table 2, table 1)

Front 57

What are two options you can add to the Create Index or Create Table statements?

Back 57

FILLFACTOR

PAD_INDEX

Front 58

What T-SQL command checks from fragmentation database-wide, table-wide, for a specific index or specific partition?

Back 58

sys.dm_db_index_physical_stats

Front 59

What is external fragmentation?

Back 59

•Pages spread (non-contiguously) within adata file

Front 60

3 ways to maintain an index

Back 60

–ALTER INDEX … REORGANIZE

–ALTER INDEX …REBUILD

–ALTER INDEX … ALL

Front 61

3 ways to maintain statistics

Back 61

–Auto create and update options

–AUTO_UPDATE_STATISTICS_ASYNC

–Forceful update with sp_UpdateStats