Recovery models in SQL Server

Share via:

Post Views: 0

Hello Readers,

In this article, we will see Recovery models in SQL Server.

SQL Server backup and restore operations occur within the context of the recovery model of the database.
Recovery models are designed to control transaction log maintenance.
A recovery model is a database property that controls how transactions are logged, whether the transaction log requires backing up, and what kinds of restore operations are available.
Three recovery models are there (they are) simple, full, and bulk-logged.

SIMPLE RECOVERY MODEL:
The “Simple” recovery model… it gives you a simple backup that can be used to replace our entire database in the event of a failure or if you have the need to restore our database to another server.
With this recovery model you have the ability to do complete backups (an entire copy) or differential backups (any changes since the last complete backup).
With this recovery model you are exposed to any failures since the last backup completed, because you will only be able to restore the data to the point when the backup occurred.
It is the most basic recovery model for SQL Server.
In every transaction we should write to the transaction log, but once the transaction is complete and the data has been written to the data file the space that was used in the transaction log file is now re-usable by new transactions.
Since this space is reused there is not the ability to do a point in time recovery, therefore the most recent restore point will either be the complete backup or the latest differential backup that was completed.
Here are some reasons why we should use this recovery model:
• Your data is not critical and can easily be recreated
• The database is only used for test or development
• Data is static and does not change
• Losing any or all transactions since the last backup is not a problem
• Data is derived and can easily be recreated

FULL RECOVERY MODEL:
In this SQL Server to keep all transaction data in the transaction log until either a transaction log backup occurs or the transaction log is truncated.
The way this works is that all transactions that are issued against SQL Server first get entered into the transaction log and then the data is written to the appropriate data file.
This allows SQL Server to rollback each step of the process in case there was an error or the transaction was cancelled for some reason.
It is the most complete recovery model and it allows to recover all of your data to any point in time as long as all backup files are useable and readable.
With this model all operations are fully logged which means that you can recover your database to any point.
If the database is set to the full recovery model you need to issue transaction log backups otherwise your database transaction log will continue to grow forever.
Here are some reasons why you should choose this recovery model:
• Data is critical and you want to minimize data loss.
• You need the ability to do a point-in-time recovery.
• You are using Database Mirroring
• You are using Always On Availability Groups

BULK LOGGED RECOVERY MODEL:
In this model there are certain bulk operations such as BULK INSERT, CREATE INDEX, SELECT INTO, etc…
That are not fully logged in the transaction log and therefore do not take as much space in the transaction log.
The advantage of using the “Bulk-logged” recovery model is that your transaction logs will not get that large if you are doing large bulk operations and it still allows you to do point in time recovery as long as your last transaction log backup does not include a bulk operation.
If no bulk operations are run, this recovery model works the same as the Full recovery model.
Here are some reasons why you should choose this recovery model:
• Data is critical and you want to minimize data loss, but you do not want to log large bulk operations
• Bulk operations are done at different times versus normal processing.
• You still want to be able to recover to a point in time

SYSTEM DATA BASES:
These are defined by Microsoft and are needed for SQL Server to operate.
These databases are include in Master, Model, MSDB, TempDB, Resource, Distribution database used in replication as well as the ReportServer and ReportServerTempDB databases used for Reporting Services

MASTER DATABASE:
The master database records all the system-level information for a SQL Server system.
It includes instance-wide metadata such as logon accounts, endpoints, linked servers, and system configuration settings.
In SQL Server, system objects have no longer stored in the master database.
The initial configuration values of the master data and log files for SQL Server and Azure SQL Managed Instance.
The sizes of these files may slightly different editions in SQL Server.

MODEL DATABASE:
The model database is used as the template for all databases created on SQL Server.
Because tempdb is created every time SQL Server is started, the model database must always exist on a SQL Server system.
The entire contents of the model database, including database options, are copied to the new database.
Newly created user databases use the same recovery model as the model database.

Model Usage:
When a CREATE DATABASE statement is issued, the first part of the database is created by copying in the contents of the model database.
The rest of the new database is then filled with empty pages.
If you modify the model database, all databases created afterward will those changes.
For example, we could set permissions or database options, or add objects such as tables, functions, or stored procedures.
File properties of the model database are an exception, and are ignored except the initial size of the data file.
The default initial size of the model database data and log file is 8 MB.
The initial configuration values and tables of the model data and log files.

msdb database:
The msdb database is used by SQL Server Agent for scheduling alerts and jobs and by other features such as SQL Server Management Studio, Service Broker and Database Mail.
For example, SQL Server automatically maintains a complete online backup-and-restore history within tables in msdb.
This information includes the name of the party that performed the backup, the time of the backup, and the devices or files where the backup is stored.
SQL Server Management Studio uses this information to propose a plan for restoring a database and applying any transaction log backups.
Backup events for all databases are recorded even if they were created with custom applications or third-party tools.
By default, msdb uses the simple recovery model. If you use the backup and restore history tables, we recommend that you use the full recovery model for msdb.
the initial configuration values of the msdb data and log files.
The sizes of these files may vary slightly for different editions of SQL Server Database Engine.

RESOURSE DATABASE:
The Resource database is a read-only database that contains all the system objects that are included with SQL Server.
SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database.
The Resource database does not contain user data or user metadata.
The Resource database makes upgrading to a new version of SQL Server an easier and faster procedure.
In earlier versions of SQL Server, upgrading required dropping and creating system objects.
Because the Resource database file contains all system objects, an upgrade is now accomplished simply by copying the single Resource database file to the local server.
The physical file names of the Resource database are mssqlsystemresource.mdf and mssqlsystemresource.ldf.
These files are located in <drive>:\Program Files\Microsoft SQL Server\MSSQL<version>.<instance_name>\MSSQL\Binn\ and should not be moved.
Each instance of SQL Server has one and only one associated mssqlsystemresource.mdf file, and instances do not share this file.

TEMPDB:
The tempdb system database is a global resource that’s available to all users connected to the instance of SQL Server or connected to Azure SQL Database.
• Temporary user objects that are explicitly created. They include global or local temporary tables and indexes, temporary stored procedures, table variables, tables returned in table-valued functions, and cursors.
• Internal objects that the database engine creates. They include:
o Work tables to store intermediate results for spools, cursors, sorts, and temporary large object (LOB) storage.
o Work files for hash join or hash aggregate operations.
o Intermediate sort results for operations such as creating or rebuilding indexes (if SORT_IN_TEMPDB is specified), or certain GROUP BY, ORDER BY, or UNION queries.
• Version stores, which are collections of data pages that hold the data rows that support features for row versioning. There are two types: a common version store and an online-index-build version store. The version stores contain:
o Row versions that are generated by data modification transactions in a database that uses READ COMMITTED through row versioning isolation or snapshot isolation transactions.
o Row versions that are generated by data modification transactions for features, such as online index operations, Multiple Active Result Sets (MARS), and AFTER triggers.
Operations within tempdb are minimally logged so that transactions can be rolled back. tempdb is re-created every time SQL Server is started so that the system always starts with a clean copy of the database. Temporary tables and stored procedures are dropped automatically on disconnect, and no connections are active when the system is shut down.
tempdb never has anything to be saved from one session of SQL Server to another. Backup and restore operations are not allowed on tempdb.