MyTechMantra.com
Connect With MyTechMantra.com

Like Us on Facebook    Follow Us on Twitter     Subscribe to our Feeds     Subscribe to NewsLetter























Latest SQL Server Tips














SQL Server FULL Recovery Model Step by Step Tutorial with Examples

Read Comments   |   Related Tutorials: SQL Server Database Backup Options > Next Topic: BULK-LOGGED Recovery Model

Page 4 / 16


Click here to read this tutorial from the beginning...

Introduction

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 transactional 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 transactional 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 transactional 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 backups
  • Transaction log backups
  • File / FileGroup backups
  • Partial backups
  • Copy-Only backups

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.

Note: - 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.

How to Change the recovery model of SQL Server Database to FULL Using SSMS

Note: - Changing the recovery model of a database will break the backup chain. Hence, as a Best Practice one should immediately take the full backup of the database after changing the recovery model.



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

Previous Page.. Next Page..


Learn More...




Last Updated On: Feb 12, 2014



Share this Article



Receive Free SQL Server Tips and Keep Learning
Get Free SQL Server Tips




Please leave your Valuable Comment or Let us know how this article helped you: