Set Database to Full Recovery Model in SQL Server

Leave a Reply
Full recovery model is SQL server is an option, which helps in keeping all the data transactions in the transaction log. The entire transaction will be held until there is a transaction log backup created. The idea is pretty simple, wherein the entire transactions are logged into the transaction log, which then gets moved to the primary file or secondary file accordingly. Usually, keeping in full recovery option can lead to increased space, since the transaction log contains all the transactions. Further, to avoid the same, there is a need to create log backup to reduce the size of transactions with held in the transaction log.

To recovery from disasters, full recovery helps in point in time recovery. One advantage of full recovery model in SQL server is that, in case if there are any failure in the transaction log, the entire process gets cancelled or revoked. In SQL Server, when a database is created, by default full recovery model is set.

Some of the reasons to Full Recovery model:
  1. Point-in-time recovery is possible with full recovery model
  2. Very huge transactions was held and the data is very critical
  3.  Protection against data loss
T/SQL script to set the database to simple recovery model

ALTER DATABASE <Database_Name> SET RECOVERY FULL

Follow the below steps in SQL Server Management Studio graphic user interface:
  1. Right on the respective database and click “properties”
  2. Select “options” from “Select a page” side bar
  3. A drop is seen under “Recovery Model”. Select “Full” from the drop down.
  4. Click “OK” to set the database to simple recovery model
The screenshot for the same tagged below:







0 comments:

Post a Comment