Skip to main content

SQL Server Recovery Models

A SQL Server database can be in one of three recovery models. FULL, BULK_LOGGED and SIMPLE. The recovery model you choose for your databases will  affect your restore plans in the event of failure or need to restore, the recovery model you pick will also influence how you mange the transaction log of your database. So you need to choose carefully.

Full

The full recovery model offers your database the most protection. Full recovery mode means all database operations are fully logged. It allows point in time and point of failure restores. It does come with some overhead though, you need to implement both Full and Log backups as part of your backup plan. You need the transaction log backups for two reasons. 1. Transaction log backups allow you to perform point of failure or point in time restores. 2. If you don’t take transaction log backups of your database when its in Full recovery mode then your transaction log will grow uncontrollably and will eventually use up all available drive space. In fact this is one of the biggest problems I have seen in my  experience, people having a database in Full recovery mode, taking regular full backups but not taking regular log backups, they only notice the problem when they run out of disk space. By backing up the log you allow the inactive portions of the log to be truncated and reused.

Bulk Logged

Bulk Logged recovery mode is not dissimilar to the Full recovery mode, Transaction log backups still need to be an integral part of your backup plan. The difference between bulk logged and full is that certain bulk operations are only minimally logged when a database is in ‘Bulk Logged’ mode. Operations such as CREATE INDEX, SELECT * INTO, BCP, BULK INSERT are only minimally logged, according to Books Online (BOL) “ Minimal logging involves logging only the information that is required to recover the transaction without supporting point-in-time recovery.” This means that if your database crashes during one of these minimally logged operations and you need to restore you will not be able to restore to a point in time.

Simple

The main advantage of the simple recovery mode is that you do not need to worry  about managing the transaction log. In fact you are not able to take transaction log backups when a database is in Simple mode. The obvious disadvantage then is that you are not able to perform a point-in-time restore when a database is in simple mode which may not be appropriate for OLTP production systems.

What do I Use?

Well in true DBA fashion, it depends. I will generalise a little here, usually for production OLTP databases I go with the full recovery model, these are business critical systems that I need to be able to recover point of failure or point in time. In this mode I will use Full backups combined with transaction log backups (differentials if needed.) on a schedule dictated by the business and what it needs in terms of recovery time. I like to copy the log backups to a different server and write the full backup to tape and store off-site. I do use the Simple recovery model on certain production systems occasionally, mainly for databases where the data remains static or does not change then I tend to go with simple with because that is usually enough and removes the need for me to manage the log.

What to consider when choosing a recover model

1. How much data loss, if any, is acceptable.

2. How often does the data change.

Setting the Recovery Model

When you create a database,  the newly created database will get it’s recovery model from the Model database.

My very simple CREATE DATABASE statement below creates a new database called ModRecTest:

CREATE DATABASE [RecModTest]
GO



The following query selects from sys.databases and selects the recovery model of both the Model database and the newly created RecModTest database:



SELECT  Name AS 'DBname', recovery_model_desc
FROM sys.databases
WHERE name = 'Model'
OR Name = 'RecModTest'

GO



Here are the results:



Img1



As you can see the recovery model is the same for both databases.



If you would like to change the recovery model of your database you can do this from object explorer in Management Studio by right clicking on the database in question selecting <Properties> move to the <options> tab and you can change the recovery model by choosing a different option from the drop-down list <Recovery Model> and select <OK> you can also run TSQL:



ALTER DATABASE [dbname] SET RECOVERY BULK_LOGGED



Is your Database Really in Full Recovery?



It is worth noting here, even though sys.databases tells me that my database is full recovery mode it is not in technically in ‘Full’ recovery until a Full database backup is taken. You can prove this in several ways:



1. If you try to run a transaction log backup against your database you will get an error saying that no full backup exists:



BACKUP LOG RecModTest TO DISK = 'C:\DATA\Backup\RecModTest.BAK'


You will get an error:



Img2



2. Another way to see if your database really is in full recovery mode is to use  the following query:



SELECT d.Name, d.recovery_model_desc, dr.last_log_backup_lsn 
FROM sys.databases d
INNER JOIN sys.database_recovery_status dr
ON d.database_id = dr.database_id
WHERE d.database_id = db_id('RecModTest')



Now if this query returns values similar to the following for your database then, even though it says it is Full recovery the lack of a value in the last_log_backup_lsn field means that a Full backup does not exist and the database is still in auto truncate (pseudo-simple) mode.



Img4



If you run a FULL database backup and run the same query again, you will see you get an LSN in the last_log_backup_lsn field. The full backup kicks off the log sequence chain and SQL Server knows to expect log backups.



BACKUP DATABASE RecModTest TO DISK = 'C:\DATA\Backup\RecModTest.BAK'


Img5 



If you create the database in simple mode, take a full backup and re-run this query you will see that the last_log_backup_lsn field is still a NULL value. This means that SQL Server does not expect log backups (It won’t allow them on a database in Simple mode) and the database in auto-truncate mode.



USE master 
GO

DROP DATABASE RecModTest



CREATE DATABASE RecModTest
GO
ALTER DATABASE RecModTest SET RECOVERY SIMPLE


Then run a full backup:



BACKUP DATABASE RecModTest TO DISK = 'C:\DATA\Backup\RecModTest.BAK'



Then re-run the above query and we can see that the last_log_backup_lsn  field is still NULL and therefore SQL Server is not expecting log backups and is auto-truncate mode.



Img3

Comments

  1. How does the recovery model affect checkpointing? It is my understanding, from a performance perspective, that the recovery model shouldn't affect how often checkpoints are taken.

    ReplyDelete

Post a Comment

Popular posts from this blog

SQL Server 2012 and Virtual Service Accounts

This post is written by David Postlethwaite
If you are using SQL Server 2012 you will probably have noticed that the default account for the SQL services has changed from that used in previous versions. With SQL 2005 and 2008 the default account for SQL service and SQL Agent service was “NT Authority\System”. This is one the built in accounts on a Windows machine, managed by the machine and selectable from a dedicated dropdown list

The Network Service account was introduced in Windows 2003 as an alternative to using the LocalSystem account, which has full local system privileges on the local machine, a major security concern.
The Network Service has limited local privileges easing these security concerns but when many services on a machine use the Network Service account it becomes harder to track which service is actually accessing resources and performing actions, because all the services are using the one Network Service account.
Also, this account, by default, has sysadmin per…

Always Encrypted

By David Postlethwaite

Always Encrypted is new features in SQL Server 2016 and it is also available in Azure SQL Database. Here you can encrypt columns in a table with a master key and a certificate so that they will appear as encrypted strings to those who don’t have the required certificate installed on their pc.
Once the certificate is installed on the computer then the unencrypted data can then be seen as normal.

The data passes from database to your application as the encrypted value, only the application with the correct certificate can unencrypt the data so it is secure across the wire. This will go some way to resolving the concern of people worried about putting their sensitive data on a shared server in the cloud such as Microsoft Azure and accessing the data across the Internet.

At the time of writing Always Encrypted is only supported with ADO.NET 4.6, JDBC 6.0 and ODBC 13.1 but expect other driver to become available.

The calling application (including SSMS) must also hav…

How to Setup Kerberos Correctly

David was in Copenhagen this weekend delivering his Kerberos talk Taming the Beast: Kerberos for the SQL DBA to SQL Saturday Denmark. I have had a quick chat with him via email since he got back and he said he had a great time. The event was very well attended with 280+ attendees and his talk was well attended.

I think David is planning submitting a few sessions to SQL Saturday events in Europe in the next few months so look out for him there and we'll keep you posted as to his whereabouts when schedules get finalised later in the year.

David has pre-recorded his Kerberos talk. You can watch on you tube and I have also embedded it in this post if you want to see what his kerberos talk  covers...



If we can help you with a SQL Sever problem visit our SQL Server Consulting page or contact us