Configure Recovery Models

A recovery model is a database configuration option that controls how transactions are
logged, whether the transaction log is backed up, and what restore options are available
for the database. The recovery model you choose for your database has both data recovery
implications and performance implications, based on the logging the recovery
model performs or doesn’t perform.

There are:

• Simple recovery model
• Full recovery model
• Bulk-Logged recovery model 

--Configure Recovery Models
SELECT name, recovery_model_desc FROM sys.databases
alter database dbname set recovery full
/*Full recovery is the recommended model for a production database
because it provides the most recoverable configuration. If you import data periodically
by using a bulk mechanism, you can temporarily change the recovery model for
your database to Bulk-Logged to get better bulk-load performance. Then, when the
import process ends, return your database to the Full recovery model.*/
-------******------
/*1. Set the database recovery model for the AdventureWorks database to Bulk-
Logged by executing the following ALTER DATABASE statement. (Before changing
the recovery model, do a full backup of the database.)
-- Note that you should create the C:\Backup folder at Operating System level before
running this backup. */
BACKUP DATABASE AdventureWorks TO DISK='C:\Backup\AdventureWorks.Bak'
GO
--Change the Recovery Model to Bulk Logged
ALTER DATABASE AdventureWorks SET RECOVERY BULK_LOGGED
/*2. Type and then run the following ALTER DATABASE statement to change the
recovery model back to Full after performing the bulk-logged operations; perform
another full database backup so that you have a backup of the data that
was just loaded:*/
ALTER DATABASE AdventureWorks SET RECOVERY FULL
--Perform a Full database backup
BACKUP DATABASE AdventureWorks TO DISK='C:\Backup\AdventureWorks.Bak'
GO