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.

This is Part 4 of 16 Part SQL Server Database Backup Tutorial. Click here to read it from the beginning….

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.

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.

Clicking Next Page button to continue reading the topics and click on the Previous Page button to revisit the previous topic.

Related Articles

• SQL Server Database Backup Tutorial with Examples
• Different SQL Server Recovery Models Step by Step Tutorial with Examples
• SQL Server SIMPLE Recovery Model Step by Step Tutorial with Examples
• SQL Server FULL Recovery Model Step by Step Tutorial with Examples
• SQL Server BULK-LOGGED Recovery Model Step by Step Tutorial with Examples
• Different Types of SQL Server Backups
• Permissions Required to Take Database Backup in SQL Server
• FULL Database Backups in SQL Server Step by Step Tutorial with Examples
• DIFFERENTIAL Database Backups in SQL Server Step by Step Tutorial with Examples
• TRANSACTION LOG Backups in SQL Server Step by Step Tutorial with Examples
• COPY_ONLY Backup in SQL Server Step by Step Tutorial with Examples
• MIRRORED Backup in SQL Server Step by Step Tutorial with Examples
• FILE Backup in SQL Server Step by Step Tutorial with Examples
• FILEGROUP Backup in SQL Server Step by Step Tutorial with Examples
• PARTIAL Backup in SQL Server Step by Step Tutorial with Examples
• TAIL-LOG Backup in SQL Server Step by Step Tutorial with Examples