MCQs on Backup and Restore | SQL Server
Explore these 30 MCQs on SQL Server Backup and Restore, covering essential topics like full, differential, and transaction log backups, restoring databases, point-in-time recovery, backup strategies, and automated scheduling. Perfect for SQL enthusiasts and IT professionals!
Full, Differential, and Transaction Log Backups
- What does a full backup in SQL Server include?
- A) Data files only
- B) Transaction log only
- C) Entire database and transaction log
- D) Configuration settings
- Which backup type includes only the changes made since the last full backup?
- A) Full Backup
- B) Differential Backup
- C) Transaction Log Backup
- D) Copy-Only Backup
- What is the primary purpose of a transaction log backup?
- A) Capture all data in the database
- B) Back up only the database schema
- C) Back up changes since the last log backup
- D) Replicate the database
- Which type of backup does not impact the existing sequence of SQL Server backups?
- A) Full Backup
- B) Copy-Only Backup
- C) Differential Backup
- D) Log Backup
- When performing a differential backup, it is based on:
- A) The last log backup
- B) The last differential backup
- C) The last full backup
- D) Any backup
- Which type of backup is most suitable for minimizing data loss in high transaction environments?
- A) Full Backup
- B) Differential Backup
- C) Transaction Log Backup
- D) Copy-Only Backup
- Can a differential backup be restored without the original full backup?
- A) Yes
- B) No
- What does the tail-log backup ensure in SQL Server?
- A) Database performance monitoring
- B) Recovery of active transactions during failure
- C) Shrinking log files
- D) Encryption of backups
Restoring Databases (Point-in-Time Recovery)
- Which recovery model is required for point-in-time recovery in SQL Server?
- A) Simple
- B) Bulk-Logged
- C) Full
- D) No Recovery
- What is the purpose of the NORECOVERY option during database restoration?
- A) Allows further transaction log backups
- B) Restores and closes the database
- C) Verifies database consistency
- D) Logs restore errors
- When using STOPAT during recovery, what does it specify?
- A) The type of recovery
- B) The time to stop the restoration
- C) The size of the backup
- D) The recovery model
- Which command can be used to restore a database from a backup file?
- A) BACKUP DATABASE
- B) RESTORE DATABASE
- C) DATABASE RECOVERY
- D) RESTORE TRANSACTION LOG
- In SQL Server, the sequence of restoration when using differential and transaction log backups is:
- A) Differential, Full, Transaction Log
- B) Full, Transaction Log, Differential
- C) Full, Differential, Transaction Log
- D) Transaction Log, Differential, Full
- What must you do after the final restore in a sequence of backups?
- A) Apply the WITH NORECOVERY option
- B) Use the WITH RECOVERY option
- C) Rebuild the database index
- D) Reconfigure the recovery model
- Point-in-time recovery is possible with:
- A) Full Recovery Model
- B) Simple Recovery Model
- C) Differential Recovery Model
- D) Log-Free Model
- A database marked as “Restoring” can be accessed for queries.
- A) True
- B) False
Backup Strategies and Best Practices
- What is the recommended frequency of full backups in critical production environments?
- A) Daily
- B) Weekly
- C) Monthly
- D) Annually
- Which backup type should be taken frequently in high-transaction environments?
- A) Full Backup
- B) Differential Backup
- C) Transaction Log Backup
- D) Copy-Only Backup
- What is the main purpose of offsite backup storage?
- A) Speed up restoration
- B) Disaster recovery
- C) Backup testing
- D) Save storage costs
- Which SQL Server feature encrypts backup files?
- A) Transparent Data Encryption (TDE)
- B) Database Mirroring
- C) Always On Availability Groups
- D) Backup Compression
- Backup verification ensures:
- A) Backups are taken frequently
- B) The backup file is consistent and usable
- C) The database is encrypted
- D) Logs are cleared
- What is a common best practice for retaining backup files?
- A) Keep backups indefinitely
- B) Retain backups according to compliance requirements
- C) Overwrite backups weekly
- D) Only store full backups
- Which tool can be used to monitor and verify backup jobs in SQL Server?
- A) SQL Server Management Studio (SSMS)
- B) SQL Profiler
- C) Windows Task Manager
- D) Database Tuning Advisor
- What is the purpose of backup compression in SQL Server?
- A) Reduce backup time and storage space
- B) Encrypt backup files
- C) Improve query performance
- D) Enable point-in-time recovery
Using SQL Server Agent for Scheduling Backups
- What is SQL Server Agent primarily used for?
- A) Managing database security
- B) Automating database tasks
- C) Enforcing backup encryption
- D) Monitoring database performance
- Which type of job step in SQL Server Agent is used to execute a database backup?
- A) PowerShell Script
- B) T-SQL Script
- C) Maintenance Plan
- D) Replication
- How do you ensure a backup job runs at regular intervals using SQL Server Agent?
- A) Create a schedule for the job
- B) Use the WITH RECOVERY option
- C) Configure a database alert
- D) Enable database snapshots
- Which SQL Server feature allows you to define a series of backup tasks as a Maintenance Plan?
- A) SQL Server Agent
- B) Query Editor
- C) Management Plan Designer
- D) Always On
- SQL Server Agent allows for email notifications on backup job status. What feature supports this?
- A) Database Mail
- B) Windows Notifications
- C) Task Scheduler Alerts
- D) Email Encryption
- Which system database stores SQL Server Agent job information?
- A) Master
- B) Model
- C) MSDB
- D) TempDB
Answer Key
| Qno | Answer |
|---|---|
| 1 | C) Entire database and transaction log |
| 2 | B) Differential Backup |
| 3 | C) Back up changes since the last log backup |
| 4 | B) Copy-Only Backup |
| 5 | C) The last full backup |
| 6 | C) Transaction Log Backup |
| 7 | B) No |
| 8 | B) Recovery of active transactions during failure |
| 9 | C) Full |
| 10 | A) Allows further transaction log backups |
| 11 | B) The time to stop the restoration |
| 12 | B) RESTORE DATABASE |
| 13 | C) Full, Differential, Transaction Log |
| 14 | B) Use the WITH RECOVERY option |
| 15 | A) Full Recovery Model |
| 16 | B) False |
| 17 | A) Daily |
| 18 | C) Transaction Log Backup |
| 19 | B) Disaster recovery |
| 20 | A) Transparent Data Encryption (TDE) |
| 21 | B) The backup file is consistent and usable |
| 22 | B) Retain backups according to compliance requirements |
| 23 | A) SQL Server Management Studio (SSMS) |
| 24 | A) Reduce backup time and storage space |
| 25 | B) Automating database tasks |
| 26 | B) T-SQL Script |
| 27 | A) Create a schedule for the job |
| 28 | A) SQL Server Agent |
| 29 | A) Database Mail |
| 30 | C) MSDB |