MS SQL Server - 66 - Backup Tasks MCQ

66 – Backup Tasks MCQ

1 / 25

1. In SQL Server, which Transact-SQL statement is used to perform a full database backup?

BACKUP DATABASE

BACKUP ALL

BACKUP DATA

BACKUP FULL

2 / 25

2. What is the primary purpose of the BACKUP DATABASE statement in SQL Server?

To back up a specific file or filegroup within the database

To back up only specific tables within a database

To create a copy of the transaction log

To back up the entire database including all data and objects

3 / 25

3. Which Transact-SQL statement is used to perform a differential database backup in SQL Server?

BACKUP DIFFERENTIAL

BACKUP PARTIAL

BACKUP INCREMENTAL

BACKUP DELTA

4 / 25

4. What does the BACKUP DIFFERENTIAL statement capture in SQL Server?

Specific file-level changes

All data and objects in the database

The entire transaction log

Changes made since the last full or differential backup

5 / 25

5. In SQL Server Management Studio (SSMS), what menu option is used to initiate a full database backup?

Tools > Options

File > Save As

View > Object Explorer

Tasks > Backup

6 / 25

6. Which SQL Server tool provides a graphical user interface (GUI) for performing database backups?

SQL Server Profiler

SQL Server Management Studio (SSMS)

SQLCMD utility

SQL Server Configuration Manager

7 / 25

7. In SQL Server, what is the purpose of the “Backup type” dropdown menu in SQL Server Management Studio (SSMS)?

To select the database to be backed up

To configure encryption settings

To specify the location of the backup file

To choose the type of backup (full, differential, transaction log)

8 / 25

8. What does the “Add” button allow you to do when configuring a backup in SQL Server Management Studio (SSMS)?

Add custom scripts for backup operations

Add users to the database being backed up

Add backup devices for storing backup files

Add a new database to the server

9 / 25

9. In SQL Server, what is the purpose of the “Media Options” section when configuring a backup in SQL Server Management Studio (SSMS)?

To specify the backup file format (e.g., .bak or .zip)

To configure encryption settings

To specify the location of the backup file

To set the backup compression options

10 / 25

10. Which SQL Server Management Studio (SSMS) feature allows you to schedule recurring database backups?

Solution Explorer

Object Explorer

Query Editor

Maintenance Plans

11 / 25

11. In SQL Server, what is the primary factor to consider when determining which databases to back up?

The number of stored procedures

The database owner

The database size

The database name

12 / 25

12. Which databases should typically be included in regular backup routines?

Both user and system databases

Only tempdb

Only user databases

Only system databases

13 / 25

13. In SQL Server, what is the purpose of backing up the system databases (e.g., master, model, msdb)?

To back up only specific tables within a database

To provide point-in-time recovery options for system-related data

To prevent accidental deletion of user databases

To speed up query performance

14 / 25

14. Which Transact-SQL statement is used to perform a transaction log backup in SQL Server?

BACKUP TRANSACTION

BACKUP REDOLOG

BACKUP TLOG

BACKUP LOG

15 / 25

15. What does the BACKUP LOG statement capture in SQL Server?

Specific file-level changes

All data and objects in the database

The entire transaction log

Changes made since the last full or differential backup

16 / 25

16. In SQL Server, which backup type is typically used for point-in-time recovery and restoring a database to a specific moment in time?

Differential Backup

File or Filegroup Backup

Transaction Log Backup

Full Database Backup

17 / 25

17. What is the primary purpose of the BACKUP PARTIAL statement in SQL Server?

To back up only specific tables within a database

To create a copy of the transaction log

To capture changes made since the last full or differential backup

To back up a specific file or filegroup within the database

18 / 25

18. Which SQL Server Management Studio (SSMS) option allows you to customize backup settings, such as compression and encryption?

Backup Preferences

Backup Configuration

Backup Templates

Backup Options

19 / 25

19. What does the “Backup Devices” option in SQL Server Management Studio (SSMS) allow you to do?

Manage backup files and devices

Set backup compression options

Configure encryption settings

Specify the location of the backup file

20 / 25

20. In SQL Server, what is the primary purpose of the “Media Sets” option when configuring a backup?

To specify the backup file format (e.g., .bak or .zip)

To select the database to be backed up

To group backup files for sequential restores

To configure encryption settings

21 / 25

21. Which SQL Server tool allows you to define custom backup schedules and policies for multiple databases?

SQLCMD utility

SQL Server Profiler

SQL Server Management Studio (SSMS)

SQL Server Configuration Manager

22 / 25

22. In SQL Server, what is the purpose of the “Automated Backup Preferences” feature in SQL Server Management Studio (SSMS)?

To specify the location of the backup file

To configure backup compression settings

To automate recurring backup tasks

To manage backup devices

23 / 25

23. Which Transact-SQL statement is used to perform a file or filegroup backup in SQL Server?

BACKUP PARTIAL

BACKUP FGROUP

BACKUP FILE

BACKUP DATA

24 / 25

24. What does a file or filegroup backup capture in SQL Server?

Specific file-level changes

All data and objects in the database

Changes made since the last full or differential backup

The entire transaction log

25 / 25

25. What is the primary advantage of performing file or filegroup backups in SQL Server?

Automatic transaction log backups

Point-in-time recovery options

Smaller backup file size

Faster backup and restore operations

Your score is

MS SQL Server – 66 – Backup Tasks MCQ