This set of SQL Server Multiple Choice Questions & Answers (MCQs) focuses on “Recovery”.
1. SQL Server backup and restore operations occur within the context of the _______ model of the database.
a) backup
b) recovery
c) restore
d) none of the mentioned
View Answer
Explanation: A recovery model is a database property that controls how transactions are logged.
2. Point out the correct statement.
a) Backup models are designed to control transaction log maintenance
b) Restore models are designed to control transaction log maintenance
c) Recovery models are designed to control transaction log maintenance
d) None of the mentioned
View Answer
Explanation: Recovery Model is one of the mechanisms which controls and manages the growth of the transaction log file.
3. How many types of recovery model is provided by SQL Server?
a) 2
b) 3
c) 4
d) 5
View Answer
Explanation: Three recovery models exist: simple, full, and bulk-logged.
4. No log backups is a feature of __________ recovery model.
a) Simple
b) Full
c) Bulk-logged
d) All of the mentioned
View Answer
Explanation: Simple recovery model is the simplest of all and maintains only a minimum amount of information in the SQL Server transaction log file.
5. Point out the wrong statement.
a) Managing databases with simple recovery model is much easier
b) Managing databases with full recovery model is much easier
c) Managing databases with bulk-logged recovery model is much easier
d) All of the mentioned
View Answer
Explanation: Manageability with Simple recovery model is much easier–no need to take transactional backups.
6. Which of the following feature cannot be used in simple recovery mode?
a) Media recovery without data loss
b) Log shipping
c) Database mirroring
d) All of the mentioned
View Answer
Explanation: Operations that require transaction log backups are not supported by the simple recovery model.
7. Which of the following recovery models requires log backups?
a) Simple
b) Full
c) Bulk
d) All of the mentioned
View Answer
Explanation: In full recovery model, no work is lost due to a lost or damaged data file and it can even recover to an arbitrary point in time.
8. How many types of backups you can run when the data is in the “Full” recovery model?
a) 5
b) 6
c) 7
d) All of the mentioned
View Answer
Explanation: Six backups if full recovery model are Complete backup, Differential backup, File and/or Filegroup backup, Partial backup, Copy-Only backup, Transaction log backup.
9. The _______ recovery model is a special-purpose model that works in a similar manner to the full recovery model.
a) Simple
b) Full
c) bulk-logged
d) None of the mentioned
View Answer
Explanation: Full Recovery and Bulk-Logged Recovery models provide the greatest protection for data.
10. Which of the following statement is used for changing Recovery Model with T-SQL?
a) ALTER DATABASE
b) ALTER TABLE
c) ALTER COLUMN
d) None of the mentioned
View Answer
Explanation: You can use ALTER DATABASE command with the SET RECOVERY option to change the recovery model of a database.
Sanfoundry Global Education & Learning Series – SQL Server.
To practice all areas of SQL Server, here is complete set of 1000+ Multiple Choice Questions and Answers.
If you find a mistake in question / option / answer, kindly take a screenshot and email to [email protected]
- Apply for Programming Internship
- Practice Programming MCQs
- Check Information Technology Books
- Check SQL Server Books
