FeedBack Form

Your Name :
Your Email :
Your Location :
Your Message :
   
FeedBack

SQL SERVER INTERVIEW QUESTION & ANSWERS

Databases
Follow Us
 
TAGS
41. What is Database Recovery Model?
  database recovery model determines if the transaction log is allowed to accumulate all changes to a database, a subset of changes, or only enough changes for database recovery. The recovery model also determines if transaction log backups are required to free space for new transactions. In cases where recovery to the last full backup is acceptable, the transaction log can be set to automatically truncate every time a new checkpoint occurs, freeing up log space.
Your Name Your Email-ID
Your Answer
 
42. Explain the Three Database Recovery Models.
  Full Recovery: When using the full recovery model, the transaction log accumulates all changes made to the database. To keep a record of all modifications, the transaction log is periodically backed up. If the transaction log isn’t backed up, the log will continue to accumulate changes until no more space is available. Therefore, a log backup strategy is very important. For most installations of SQL Server, this is the default recovery model.
Simple Recovery: When using the simple recovery model, the transaction log accumulates enough information to recover the database in the event of a system failure. Transactions are periodically truncated from the transaction log to free up disk space and shorten the recovery time. When a database is using the simple recovery model, only full and differential backups are allowed. Transaction log backups are not used. Therefore, restoring the database is only possible up to the last full or differential backup. Simple recovery is most appropriate for databases that can easily be re-created in the event of a hardware failure, and where a certain amount of data loss is acceptable.
Bulk-Logged Recovery: The bulk-logged recovery model is similar to the full recovery model, but in it less information about certain bulk operations is written to the transaction log. An advantage of this model is that less space is used for bulk operations, thereby minimizing the amount of space required for backups. Because less information is logged about these operations, only full transaction log backups can be restored; point-in-time recovery is not supported.
Your Name Your Email-ID
Your Answer
 
43. What statement do you use to view the recovery models of all user databases on a SQL Server instance?
  SELECT name, recovery_model_desc FROM sys.databases
Your Name Your Email-ID
Your Answer
 
44. What statement do you use to modify the recovery model of database?
  ALTER DATABASE database_name SET RECOVERY { SIMPLE | FULL | BULK_LOGGED }
Your Name Your Email-ID
Your Answer
 
45. SMO stands for?
  SQL Server Management Objects
Your Name Your Email-ID
Your Answer