Role Of Simple Recovery Model in Database Recovery

Overview

In Simple Recovery Model, the individual purpose of the transaction log is to provide data consistency and integrity during the normal operation performed, transaction log returns the database to a consistent state if database detects any error or issues a transaction rollback, or during crash recovery. The logs are used to roll back the modification done by an incomplete transaction. Transaction log backup in Simple Recovery Model cannot be performed, so there is no use of log backups in any restore operations. This means the database cannot be recovered to a specific point within a backup.

Note: In this model transaction log is automatically truncated after a checkpoint is created, mainly eliminating the need to manage the transaction log space.

Impact of Backup Under Simple Recovery Model

Backup under the simple recovery model supports both database backup and file backups. And benefits of simple recovery model is that log backup is not supported, due to which managing backup and restore become easy. However, database can be restored only with the recent backup available. As no transaction log backup is performed so chances of work loss increases over time until the next differential or full backup taken. Therefore, it is recommended that backup should be performed regularly to avoid losing a huge amount of data.
Work-loss exposure can be reduced by performing following backup strategy:

• Perform a full database backup.
• After the first full database a series of three or four differential backups taken.
• The last differential backup can be of larger size as next backup performed is full database backup.
• Then the next full database backup is performed. After this, new differential backups are performed in same manner.

Under Simple recovery model, backup of most recent changes after a differential backup cannot be performed in case any error occurs. That’s why cannot be used where loss of recent changes is not accepted.

Performing full backup for a new media set: (Using T-SQL)

BACKUP DATABASE Demo
TO DISK = 'D:\SQLServerBackups\DemoSimpleRM.bak'
WITH FORMAT;
GO

Point in Time Recovery in Simple Recovery Model

Point in time restore database is not possible in Simple Recovery Model. Point in time recovery is restoring a database to a specified date and time. As transaction log backup in simple recovery model is not performed that’s why point in time recovery is not supported. In full recovery model point in time recovery is possible as in this model all the series of backups(full backup, differential backup, transaction log backup) are present. Therefore, in full recovery model while restoring a database no data loss occurs whereas in simple recovery model while restoring database data loss occur.

When to Use Simple Recovery Model?

Different situations are discussed below when simple recovery model is used:

• When data can be recreated and loss of data may not cause any problem.
• Point in time recovery of database is not needed.
• Losing transaction log since last backup does not create any issue.
• When data loss due to disaster could be recreated.
• Database using simple recovery model are used for test or for development.

Switching to Simple Recovery Model

In a database switching from one recovery model to another can be model can be performed easily. Here, explained switching to simple recovery model using:

Using T-SQL: ‘Demo’ database to ‘Simple Recovery Model’.

ALTER DATABASE Demo SET RECOVERY SIMPLE
GO

Using SQL Server Management Studio:

• Right click Database and go to Properties
• In properties go to Option page
• Select Simple under Recovery model
• Click OK to save

Benefits of Simple Recovery Model:

• SQL Server maintains only few information in the transaction log.
• The transaction log are truncated each time SQL Server database in simple recovery model reaches to checkpoint. Due to this log space is automatically managed.
• Requires least administration so it is easier to manage.
• Reclaims log space to keep space requirements small.
• Permits high performance.

Disadvantage of Simple Recovery Model

• In Simple recovery mode the backup of transaction log is not possible, So it is not possible for a database user to recover accidentally deleted data from a table in SQL Server database if the recovery model set to Simple.
• In simple recovery model transaction log backup not supported due to which point in time recovery is not possible, database can be restored to the exact time when a full or differential backup occurs.
• May cause huge amount of data loss if a file is damaged.
• The changes done since the most recent database backup must be redone.

Conclusion

The Simple recovery model does what it indicates, a simple backup created that can be used to replace your database in case any error occur. With this simple recovery model you have the ability to perform full backups or differential backups. As, discussed point in time recovery is not supported in simple recovery model because no transaction log backup is performed. Also, we have discussed benefits of simple recovery model and disadvantage of simple recovery model.