Questions

Q1. Which backup types can be performed in simple recovery model?

Q2. Dose a full backup truncate the transaction log?

Q3. Dose a differential backup truncate the transaction log?

Q4. Is it possible to take  differential database backup without taking a full database backup first?

Q5. The restore of a SQL Server 2012 database passes through three phases. Name those three restore phases ?

Q6. SQL Server 2012 provides a set of rich tools for performing data transfer task. Name most common Data transfer tools?

Q7. Name two Authentication modes supported by the SQL Server?

Q8. SQL Server provides server-level roles to manage the permissions on a server.  SQL Server provides nine fixed server roles. Name five fixed Server Roles?

Q9. SQL Server provides database-level roles to manage the permissions on particular database.  SQL Server provides nine fixed database roles. Name five fixed database Roles?

Q10. Principals are entities that can request and can be granted access to SQL Server resources. SQL Server has a variety of type of principals. List available SQL Server principals?

Answers

Q1. Correct Answer

Full backup

Differential backup

Partial backup

File/Filegroup backup

Copy-Only backup

Q2. Correct Answer

No, full database backup saves all the data pages in the database, and also save the active portion of the transaction log. But Transaction Log backups are the only backups that truncate the transaction log of committed transactions.

Q3. Correct Answer

No, differential backup saves all the data that have been modified since the full database backup and also saves the active portion of the transaction log. But Transaction Log backups are the only backups that truncate the transaction log of committed transactions.

Q4. Correct Answer

No, you cannot create a differential database backup unless a full database backup has been taken first.

Q5. Correct Answer

Data Copy

Redo

Undo

Q6. Correct Answer

Bulk Copy Program (bcp)

BULK INSERT

OPENROWSET

SSMS Import/Export Wizard

XML Bulk Load

Q7. Correct Answer

Windows Authentication Mode

SQL Server and Windows Authentication Mode (Mixed Mode)

Q8. Correct Answer

sysadmin

serveradmin

securityadmin

processadmin

setupadmin

bulkadmin

diskadmin

dbcreator

public

Q9. Correct Answer

db_owner

db_securityadmin

db_accessadmin

db_backupoperator

db_ddladmin

db_datawriter

db_datareader

db_denydatawriter

db_denydatareader

Q10. Correct Answer

Windows-level principals

SQL Server-level principals

Database-level principals