SQL Server Full Recovery Model For Log Backup

Introduction: SQL Server Recovery Model

With SQL Server 2005 edition, a number of data recovery models have been introduced by Microsoft. These techniques offer contrasting benefits and limitations, but at the end, they are meant to serve a better data recovery option. These recovery models share a distinct property that points to the type of technique that is used for safeguarding the database. The make changes to the database recovery model used, the ALTER statement is used. There are three basic data recovery models available for SQL Server:

1. Simple Recovery Model: This is one of the simplest and easy to implement model.
2. SQL Server Full Recovery Model: It is complicated, but definitely a robust approach towards a DR plan.
3. Bulk-Logged Recovery Model: Comparative to FRM, this gives fast performance, but is limited in benefits that FRM provides.

In this segment, we are going to discuss the SQL Server full recovery model for log backup in details, covering how it works, types of backup options, and how to set up the full recovery model using the SQL Server Management Studio.

Overview: SQL Server Full Recovery Model

When full recovery model is chosen, the transaction data will be saved into the transaction log for the time a backup of the transaction log is taken or it is truncated. When a transaction is issued against the SQL Server, it is first written to the log file before it is written to the database. This way, it is easy for SQL Server to rollback even if there occurs an error or the transaction is canceled. So, with the SQL Server full recovery model, there is a benefit of restoring the data for the point in time, i.e. the data for the time before the transaction has occurred can be recovered.

Explanation:

Until you have the data that is in usable state and its complete recovery is possible if the full RM is maintained. The benefit of adopting this SQL Server recovery model is everything gets backed up to any point in time. Not only the database, but the transaction logs will get backed up. This model proves ideal in situations when:

• The data is mission-critical and its loss in unaffordable.
• There is requirement to have a point-in-time recovery.
• Database mirroring is used as a part of database mirroring.

And when the SQL Server full recovery model is selected, there is facility to create following types of backups:

• Complete and Differential Backup
• File or Filegroup Backup
• Copy-Only/Partial Backup
• Transaction Log Backup

By, this we get to know about the importance of full recovery model in SQL Server.

Using SSMS to Set SQL Server Full Recovery Model:

• Open SSMS. Right click on the database for which full recovery model as to be assigned and choose Properties.
• Move to the Options page and use the drop-down menu to select recovery model as “Full”
• Click OK to save the changes made.

Using T-SQL to Set SQL Server Full Recovery Model:

ALTER DATABASE AdventureWorks SET RECOVERY FULL
GO

This statement is used for setting SQL Server database to Full Recovery Model.

Conclusion:

This section helps you in creating database backup using SQL Server full recovery model. Creating a full backup of the database is extremely simple and useful too. It particularly aims at creating a point in recovery model where backup of database along with their transaction logs can be generated.