SQL Server FULL Recovery Model Step by Step Tutorial with Examples

In Full Recovery Model Point in Time recovery of the database is possible as long as you have all the valid database backups along with the transaction log tail backup file. In Full Recovery model all the transactions are retained within the transaction log file until the log file is backed up or the transaction log file is truncated.

Internally how this works in SQL Server, is that all the transactions that are issued against SQL Server will first get recorded within the transaction log file and then based on success or failure/rollback/cancelled of the transaction the data gets written to data file. This actually helps SQL Server to rollback a transaction in case of an error or a user has requested for a rollback. Point in Time recovery is nothing but recovering the data to a point right before a transaction which would have resulted in accidental deletion of data from a table.

In Full recovery model all the bulk operations such as SELECT INTO, BULK INSERT, BCP, CREATE INDEX, ALTER INDEX, and REBUILD INDEX etc are fully logged and it can be recovered using the available backups.

When to choose Full Recovery Model for a database in SQL Server?

• Data is very critical for the organization and cannot afford any data loss.
• One would like to achieve Point In Time recovery of a database
• If you would like to configure and use High Availability option Database Mirroring

Different types of backups which can be performed when a database is in FULL Recovery Model are:-

• Full Backup
• Differential Backup
• Transaction Log Backup
• File / FileGroup Backup
• Partial Backup
• Copy-Only Backup

By default whenever a new database is created it will be created using FULL Recovery Model. This is because Model Database is configured to run under Full Recovery Model. When you are using Full Recovery Model database administrator should use a combination of Full, Differential and Transactional Log backups as part of database backup plan to avoid any data loss.

As a Best Practice database administrator should make sure that all the user databases in a Production environment are configured to use FULL recovery model and you are using a combination of Full, Differential and Transactional Log backups as part of database backup plan to avoid any data loss.

How to Change Database Recovery Model to FULL Using TSQL Command

ALTER DATABASE MyTechMantra SET RECOVERY FULL
GO

How to Change Database Recovery Model to FULL Using SSMS

• Connect to SQL Server Instance using SQL Server Management Studio
• Expand Database Node and then right click the user Database and select Properties from the drop down menu
• Click Options Page on the right side pane as highlighted in the below snippet
• Under Recovery Model choose FULL and click OK to save.