Hello everyone, today we are going to talk to you about several ways to get recovery mode in SQL Server . Each recovery mode has its pros and cons. That is why we are going to help you find the current recovery mode of your databases. In this way you can make the most appropriate decision in each case.
What is recovery mode?
First of all, we need to know what recovery mode is. The recovery mode is the way to define how we can recover our database if necessary. Although there are 3 modes , the most common is to use FULL recovery mode or SIMPLE recovery mode. The first of them is the one that is established by default in a zero installation of SQL Server.
The main advantage of the FULL recovery mode is that it gives us the most guarantee when it comes to recovering. As it is saving everything in the log, it can be recovered at a moment in time. The problem with this mode is that it requires a log backup, so we will need more space. This log can become worrying in massive operations, index maintenance, etc. Getting to grow even more than the size of the database itself. Once the log backup is done, that space is already recovered.
The disadvantages that FULL recovery mode has are:
- The great growth of log in some moments.
- The need to perform log backup and have additional space for this type of backup.
- The need to restore the full backup and subsequent log backups in the event of a full recovery.
As we see the FULL recovery, it is more oriented to production environments since it gives us more recovery margin. In exchange, it requires more disk capacity and the need to maintain the log backup. Additionally, its recovery is more complex since it has to recover all the log backups from the last full.
SIMPLE recovery mode
SIMPLE recovery mode has the following advantages:
- It is not necessary to make a log backup. Therefore the space required for copies is less.
- Its recovery is simple, since it would be worth recovering the last full backup.
These two points are its advantages and also its weaknesses. Due to this absence of the log backup, the way to recover the database would be to restore the last full backup. Which implies being able to lose a day of work or the time interval in which the backup is made.
Let’s take an example. A database experiences a data wipe at 16:33. Two scenarios open depending on the type of recovery:
In both cases, a full backup is made at 12 midnight and additionally a log backup every half hour if it is full.
If it was in SIMPLE recovery mode, we would lose all data since 00:00 last night regardless of when we restore.
If it were in FULL recovery mode with log backup every half hour, we could recover at 16:32, since it would be between the 16:30 and 17:00 log backups. If instead of doing it every half hour, the log backup were done every 4 hours and the last one was at 1:00 p.m., we would lose the data between 1:30 p.m. and 4:33 p.m.
Which recovery mode in SQL Server is more convenient?
As in almost everything, it depends. In this decision, the data time that we are willing to lose, the use that is going to be given to the database, and the available disk space come into play. In a development environment it may be more interesting to apply a SIMPLE recovery mode, and a FULL recovery mode in production. Always taking into account the greater complexity when managing the latter and the space and maintenance requirements it requires.
Perhaps in the billing database a FULL mode is more convenient. But in the database of the company’s website, which does not have as much movement nor is it so critical. It is more convenient to set a SIMPLE mode.
If you do not know which recovery mode is more convenient in your case, or you prefer that we take care of implementing the full backup. Do not hesitate to contact us without obligation.
We hope it has been useful to you. If you don’t want to miss these posts, subscribe to our monthly newsletter. You will be informed of our publications with a single email per month.
Still do not know Query Performance ? Find out how it can help you in your Oracle environment. More information on their page LinkedIn .
Follow GPS on LinkedIn