Chetna Bhalla
Tutorials:
SQL Server Database Backup Options >
Next Topic: BULK-LOGGED Recovery Model
Page 4 / 16
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.
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 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.
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
GOHow 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.
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.
