SQL Server Articles, SQL Server Tips, SQL Server Tutorials, SQL Server Tuning, SQL Server DBA, SQL Server Basics, Training, etc - MyTechMantra.com

SQL Server FULL Recovery Model Step by Step Tutorial with Examples

Previous Page.. Begin Tutorial.. Next Page..

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

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


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 Database Recovery Model to Full Recovery Model
How to change Database Recovery Model to Full Recovery Model

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.. Begin Tutorial.. Next Page..

Related Articles

Chetna Bhalla


Chetna Bhalla, the founder of MyTechMantra.com, believes that by sharing knowledge and building communities, we can make this world a better place to live in. Chetna is a Graduate in Social Sciences and a Masters in Human Resources and International Business. She is an alumnus of Vignana Jyothi Institute of Management, Hyderabad, India. After graduation, Chetna founded this website, which has since then become quite a favorite in the tech world. Her vision is to make this website the favorite place for seeking information on Databases and other Information Technology areas. She believes that companies which can organize and deploy their data to frame strategies are going to have a competitive edge over others. Her interest areas include Microsoft SQL Server and overall Database Management. Apart from her work, Chetna enjoys spending time with her friends, painting, gardening, playing the violin, and spending time with her son.

Newsletter Signup! Join 15,000+ Professionals

Be Social! Like & Follow Us

Follow us

Don't be shy, get in touch. We love meeting interesting people and making new friends.