Basics

Question 1

By default, SQL Server provides you with four main systems databases:

Answer 1

1.master
2.msdb
3.model
4. tempdb

Question 2

The master database stores (General)

Answer 2

all the system-level information of an SQL Server instance

Question 3

The master database stores (Specific)

Answer 3

1. Server configuration settings
2. logon accounts
3. linked servers information
4. startup stored procedure
5. file locations of user databases

Question 4

When working with the master DB you should

Answer 4

Always have a current backup

Question 5

Backup the master db ASAP after

Answer 5

1. Creating, modifyinig, and dropping any databases
2. Changing the server configurations
3. Update the logon accounts

Question 6

Basic Principles when using the master DB

Answer 6

1. Always have a current backup
2. backup the master DB after performing operations
3. Do not create user objects in the master database
4. Do not set the trustworthy database property of the master to on

Question 7

Trustworthy Database property

Answer 7

SQL Server will trust the database and the contents within it which increase the security risk

Question 8

msdb

Answer 8

1. Used by the SQL Server Agent for scheduling jobs and alerts.
2. stores the history of the SQL Agent jobs.

Question 9

msdb supports

Answer 9

1. Jobs & alerts
2. database maile
3. service broker
4. the backup and restore history for the databases

Question 10

model db

Answer 10

template for creating other databases

Question 11

tempdb

Answer 11

Stores temporary user objects that you explicitly create like temporary tables and table variables

Question 12

Can you backup or restore the tempdb?

Answer 12

No, SQL Server recreates the tempdb every time it starts

Question 13

SQL Server Recovery Model

Answer 13

1. Property of a database
2. Controls how SQL Server logs the transactions for the database
3. Whether the transaction log of the database requires backing up
4. What kind of restore operations are available for restoring the database

Question 14

SQL Server provides you with three recovery models:

Answer 14

1. Simple
2. Full
3. Bulk-logged

Question 15

To view the recovery model of the HR database, you use the following query:

Answer 15

SELECT
name,
recovery_model_desc
FROM master.sys.databases
WHERE name = ‘HR’;

Question 16

To change the recovery model to another

Answer 16

ALTER DATABASE database_name
SET RECOVERY recovery_model

Question 17

In the SIMPLE recovery model

Answer 17

1. SQL Server deletes transaction logs from the transaction log files at every checkpoint
2. Transactions logs do not store the transaction records

Question 18

FULL recovery model

Answer 18

SQL Server keeps the transaction logs in the transaction log files until the BACKUP LOG statement is executed

Question 19

FULL recoverty model allows you to

Answer 19

Restore the database at any point in time

Question 20

BULK_LOGGED recovery model

Answer 20

1. Similar to full recovery model
2. Does not allow you to restore the database at any point in time
3. Allows you to BULK INSERT flat files into tables are described briefly in the transaction log files

Question 21

What is a Backup

Answer 21

A backup is an image of that database at the time of the full backup

Question 22

Backup Types

Answer 22

1. Full Backup
2. Differential Backup
3. Transaction log backup

Question 23

To restore the database you need

Answer 23

A full backup

Question 24

How to perform a full backup

Answer 24

BACKUP DATABASE database_name
TO DISK = path_to_backup_file;

Question 25

Differential Backup

Answer 25

Contains only data that has been modified since the last full backup

Question 26

Transaction log backup

Answer 26

Contains all changes made to the database

Question 27

Before creating a user that accesses the databases in a SQL Server

Answer 27

1. Create a login for SQL Server 2. Create a user and map the user with the login

Question 28

Create Login SQL

Answer 28

CREATE LOGIN login_name WITH PASSWORD = password;

Question 29

Create User SQL

Answer 29

CREATE USER username FOR LOGIN login_name;

Question 30

A Securable

Answer 30

is a resource to which the SQL Server authorization system regulates access. ex. a table is a securable

Question 31

A principal

Answer 31

is an entity that can request the SQL Server resource ex. a user is a principal in SQL Server

Question 32

GRANT basic SQL Syntax

Answer 32

GRANT permissions ON securable TO principal;

Question 33

REVOKE basic SQL Syntax

Answer 33

REVOKE permissions ON securable FROM principal;

Question 34

Roles

Answer 34

A group of permissions

Question 35

3 main role types

Answer 35

1. Server-level roles 2. Database-level roles 3. Application-level roles

Question 36

Server-level Roles

Answer 36

Manage the permissions on SQL Server-like changing server configuration

Question 37

Database-level roles

Answer 37

Manage the permissions on databases like creating tables and querying data

Question 38

Application-level roles

Answer 38

Allow an application to run with its own, user-like permissions

Question 39

For each type of role there are two types

Answer 39

1. Fixed server roles 2. User-defined roles

Question 40

Fixed server roles

Answer 40

The built roles provided by SQL Server. These roles have a fixed set of permissions

Question 41

User-defined roles

Answer 41

The roles you define to meet specific security requirements

Question 42

db_datareader

Answer 42

Allows all members to read data from all user tables and views in the database