RECOVERY MODEL INTERVIEW QUESTIONS.

Recovery Models

1)    What is Recovery Model? Benefits of Recovery Models?

“A recovery model is a Database property that control the basic behavior of the backup and restore operations for a DB”. Recovery models are designed to control transaction log maintenance.

2)    Explain Full, Bulk-Logged & Simple Recovery Models?

Based on the importance of the data, recovery models configured to the Database.

a)  Full:- Every transaction logged into transaction log file.we can recover the data up to point in time.all production should be full.growth rate should be dragstic.

a)  Bulk-Logged:- Every transaction log file logged into transaction log file except bulk operations.point in time recovery possible bulk operations  are not done. bulk operations are minimally logged.

b)  Simple:- Each and every transaction file logged into the transaction log file when ever check point occur it truncates the transaction log file . transaction log back backup not possible.

3)    What are the differences between Full and Simple Recovery models?

Sl	Full	Simple
1	T. Logs are maintained and can recover the data up to the point of failure	T. Logs are not maintained and can recover the data up to recent full/diff backup.
2	Supported for Logshipping, Database mirroring and Replication	Not supported for Logshipping, Database mirroring and Replication
3	Maintenance will be huge	Maintenance will be less
4	Support for OLTP systems	Support for Data warehouses or not often changed databases

4)    Which Databases can we use Simple Recovery Model?

Simple  Recovery  Model  is  useful  for Development, Test databases, Data warehouses or not often changed Databases.

5)    In which recovery models Point-in-time recovery is possible?

Point-in-Time Recovery is Possible only in Full and Bulk-Logged Recovery Models, but in Bulk-Logged Recovery model Point-in-time recovery may or may not possible.

6)    What is the default recovery model for system databases?

Master              - Simple

Model                - Full

MSDB                - Simple

TempDB            - Simple

7)    Why can’t take T.Log backups in simple recovery model?

In Simple Recovery Model the Transaction Logs are truncated.

8)    How to set Recovery models using T_SQL?

a)    To Check current recovery model:- SELECT DATABASEPROPERTYEX('ADVENTUREWORKS', 'RECOVERY') As [Recovery Model]

b)    To set Simple Recovery model:- ALTER DATABASE ADVENTUREWORKS SET RECOVERY SIMPLE

c)    To set Bulk-Logged Recovery model ALTER DATABASE ADVENTUREWORKS SET RECOVERY BULK_LOGGED

d)    To set Full recovery model ALTER DATABASE ADVENTUREWORKS SET RECOVERY FULL