To log backup or not to log backup

SQL Server has been around for decades, but I still come across companies that do not have dedicated DBAs and do not understand the concept of Log Backups, which often means they put their data at risk.

Recovery Objectives

Let’s start with the basics. The best way to pick the right backup strategy is to understand Recovery Time Objective (RTO) and Recovery Point Objective (RPO). These two are the most critical parameters in planning and designing a Disaster Recovery Strategy for SQL Server and any IT system.

RTO

The recovery time objective tells us how much time we have to recover from a disaster, from when the disaster starts to when the problem is entirely resolved.

For example, If someone drops the production database and it takes two hours to restore it from backup, we would be looking at an RTO of 2 hours to restore the database plus any additional time required to figure out what has happened, find the backups, find the restore scripts and so on.

If you have an RTO requirement of 15 minutes and restoring the database from backup takes 2 hours, you need to think about ways to minimise the restore time.

We will discuss ways to reduce recovery times in future posts.

RPO

The recovery point objective tells us how much data we are accepting to lose.

For example, If someone drops the production database at 2 pm and the last backup was at 8 pm the previous day, we will lose 18 hours’ worth of data. This is because any data that has been written since the last backup is now gone.

Since we already established that it would take us 2 hours to back up or restore the database in question, there is no way we could run a backup every 5 minutes to satisfy your RPO requirement. In such a case, we need to look at different backup techniques.

SQL Server Transaction Log

To understand how backups work in SQL Server, we must start with the Transaction Log.

SQL Server Database consists of a minimum of two files: the Main Database File (MDF), where our data is kept and the Log Database File (LDF), where the information about transactions is kept:

Relational databases have transactions to comply with ACID properties. When we write data into the SQL Server database, we create a transaction.

Explicit Transactions

Explicit transaction means that we BEGIN and COMMIT our own transaction:

BEGIN TRANSACTION

INSERT INTO dbo.users (Name, Surname)
VALUES ('Marcin','Gminski')

COMMIT TRANSACTION

The benefit of using explicit transactions is that we can undo all the changes with the ROLLBACK command:

BEGIN TRANSACTION

INSERT INTO dbo.users (Name, Surname)
VALUES ('Marcin','Gminski')

ROLLBACK TRANSACTION

Implicit transactions

The SQL Server handles implicit transactions for us, and there is no way back unless the query fails. SQL Server will undo it for us to satisfy ACID properties:

INSERT INTO dbo.users (Name, Surname)
VALUES ('Marcin','Gminski')

It is, however, always a transaction.

The purpose of the Transaction Log file

The transaction log is like a journal and contains information about all the changes (transactions) we have made in our database in chronological order. The data goes into the MDF, and the information about it, the metadata, goes into the LDF. This allows us to “re-run” all transactions and recreate the data in the MDF at a given point in time. Suppose we have run the following query:

INSERT INTO dbo.users (Name, Surname,Address)
VALUES ('Marcin','Gminski','Street 1');

UPDATE dbo.users
SET Address = 'Street 2'
WHERE Name = 'Marcin'
AND Surname = 'Gminski';

We have inserted a record into the database with values Marcin, Gminski, Street 1. We have then immediately updated this record and changed the Address to Street 2.

After the above operations, the “final” Address value in the Data File (MDF) is Street 2 but the transaction log (LDF) would contain both operations. The original insert and then update (and all sorts of other information that is not easily readable by us humans).

Well, that’s not entirely true. What is kept in the Transaction Log File would depend on the database’s recovery model.

You can even run a query to see the content of the log file:

SELECT * FROM sys.fn_dblog(NULL,NULL)

Database Recovery Models in SQL Server

Simple

In the Simple recovery model, once the transaction is committed and data is safe in the Data File (MDF), the information contained in the Transaction Log (LDF) is discarded. This keeps the log file empty and relatively small but prevents us from being able to “recreate the history” or “go back in history”.

This option is suitable for databases with large RPO or where data can be reloaded from the source at any time – for example, from flat files like in a Data Warehouse “Landing Zone”.

In this mode, we can only take Full or Differential Database backups. This usually happens once a day, at night. The most recent recovery point will be the last database backup.

Simple Recovery model without Log backup

Full

In the Full recovery model, once the transaction is committed and data is safe in the Data File (MDF), the information in the Transaction Log (LDF) is retained. This keeps the log file full of information which can grow very large very quickly, but it also allows us to recreate the history. But what can we do to keep it small? We back it up!

Backing up Transaction Log Files “moves” the information from the LDF into the backup file.

YOU MUST BACK UP the transaction log frequently to be able to recover information from it. You can ONLY recover from a Log BACKUP. It is NOT ENOUGH to “just have” the transaction log without any backups.

Simple Recovery model with Log backup

Since log backups contain only the information since the last log backup, they are relatively small, but you need them all to recover the database. Do not delete any log backups since the last full database backup.

The recovery process would be to restore the nightly backup from 8 pm and then “roll forward” all the transaction log backups to the most recent one. Since there may be many backup files to restore, you best have this automated. The total recovery time may still be very long, and although you will satisfy your RPO and not lose a lot of data, you may still breach your RTO requirements because the recovery process takes too long.

This option is best for databases that require very low RPO. If your RPO is 1 minute, you must back up the transaction log at least once every minute.

Bulk Logged

This is like a Full recovery model but can switch to the Simple Recovery model and then back to Full. This is very handy if we have a very large operation that we do not want to go into the Transaction Log File (LDF) to save space and improve performance.

Performance impact

A common question: is there a performance difference between Simple and Full recovery models? No, there isn’t as far as database performance is concerned, but since you will be running lots of log backups, there will be some overhead in CPU and IO. How much will depend on many factors, likely negligible, but you will have to test it yourself.

Remember, though. This isn’t about performance but how much data you can lose. Relational databases come with a performance overhead, such as ensuring ACID properties, managing referential integrity, constraints, indexes, statistics, etc. Frequent log backups will be one of them. If we didn’t need any of the benefits of the relational database, we would have just kept all the data in a *.txt file, or NoSQL database.

Demo time

Ok, we have explained the basics, and I can now present some facts and demos. Let’s go back to our previous example of INSERT followed by an UPDATE statement:

INSERT INTO dbo.users (Name, Surname,Address)
VALUES ('Marcin','Gminski','Street 1');

UPDATE dbo.users
SET Address = 'Street 2'
WHERE Name = 'Marcin'
AND Surname = 'Gminski';

Let’s clean the table and start from scratch:

DELETE FROM dbo.users;

Let’s now insert our data:

Before the UPDATE Let’s take a log backup:

BACKUP LOG [LOGBACKUPTEST] TO DISK = 'R:\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\Logbackuptest_log1.bak'

And run our update:

And take another log backup:

BACKUP LOG [LOGBACKUPTEST] TO DISK = 'R:\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\Logbackuptest_log2.bak'

Ok, so at this point the Address value in the table dbo.users is showing Street 2. So how do we go back so it shows Street 1. Well, we have to invoke the recovery process:

  1. Restore the database from the Database Backup.
  2. Restore the first transaction log — this contains the first value

For simplicity, we can also do so via the SSMS GUI if we go to “Database” -> “Tasks” -> “Restore” -> “Database” to see all data (Full) backups and Transaction Log backups. We can then cherry-pick what to restore:

USE [master]

RESTORE DATABASE [LOGBACKUPTEST] FROM  DISK = N'R:\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\Logbackuptest.bak' WITH  FILE = 1,  NORECOVERY,  REPLACE, NOUNLOAD,  STATS = 5

RESTORE LOG [LOGBACKUPTEST] FROM  DISK = N'R:\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\Logbackuptest_log1.bak' WITH  FILE = 1,  NOUNLOAD,  STATS = 5

Let’s see what’s in our table:

We now have the first value before we run our UPDATE.

Let’s now restore to the second log backup:

USE [master]

RESTORE DATABASE [LOGBACKUPTEST] FROM  DISK = N'R:\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\Logbackuptest.bak' WITH  FILE = 1,  NORECOVERY,  REPLACE, NOUNLOAD,  STATS = 5

RESTORE LOG [LOGBACKUPTEST] FROM  DISK = N'R:\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\Logbackuptest_log1.bak' WITH  FILE = 1,  NOUNLOAD,  NORECOVERY, STATS = 5

RESTORE LOG [LOGBACKUPTEST] FROM  DISK = N'R:\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\Logbackuptest_log2.bak' WITH  FILE = 1,  NOUNLOAD, STATS = 5
GO

And the Address value has been “rolled forward” and now shows the value as modified by the UPDATE statement:

Yep. That’s how the recovery to a point in time works in SQL Server. You need those log backups done often!

Repeat after me:

I will be backing up the Transaction Log backup frequently to be able to restore to a point in time.

Complete script for you to play with:

USE [master];
-- Create test database:
CREATE DATABASE [LOGBACKUPTEST];

GO

-- Make sure we are running in Full Recovery model:
ALTER DATABASE [LOGBACKUPTEST] SET RECOVERY FULL WITH NO_WAIT;

GO

USE [LOGBACKUPTEST];

GO

-- Create test table:
CREATE TABLE dbo.users (
	Name varchar(50),
	Surname varchar(50),
	Address varchar(100)
);

-- Create the first full baseline backup:
BACKUP DATABASE [LOGBACKUPTEST] TO DISK = 'R:\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\Logbackuptest.bak';

-- Insert first set of data:
INSERT INTO dbo.users (Name, Surname,Address)
VALUES ('Marcin','Gminski','Street 1');

select * from dbo.users;

-- Backup the transaction log:
BACKUP LOG LOGBACKUPTEST TO DISK = 'R:\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\Logbackuptest_log1.bak'

-- run update and change Address to Street 2:
UPDATE dbo.users
SET Address = 'Street 2'
WHERE Name = 'Marcin'
AND Surname = 'Gminski';

select * from dbo.users;

-- Take the second log backup:
BACKUP LOG LOGBACKUPTEST TO DISK = 'R:\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\Logbackuptest_log2.bak'

-- Restore to a second log backup:
USE [master]

RESTORE DATABASE [LOGBACKUPTEST] FROM  DISK = N'R:\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\Logbackuptest.bak' WITH  FILE = 1,  NORECOVERY,  REPLACE, NOUNLOAD,  STATS = 5

RESTORE LOG [LOGBACKUPTEST] FROM  DISK = N'R:\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\Logbackuptest_log1.bak' WITH  FILE = 1,  NOUNLOAD,  STATS = 5

GO

USE [LOGBACKUPTEST]
select * from dbo.users;

-- Restore to the second log backup:
USE [master]

RESTORE DATABASE [LOGBACKUPTEST] FROM  DISK = N'R:\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\Logbackuptest.bak' WITH  FILE = 1,  NORECOVERY,  REPLACE, NOUNLOAD,  STATS = 5

RESTORE LOG [LOGBACKUPTEST] FROM  DISK = N'R:\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\Logbackuptest_log1.bak' WITH  FILE = 1,  NOUNLOAD,  NORECOVERY, STATS = 5

RESTORE LOG [LOGBACKUPTEST] FROM  DISK = N'R:\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\Logbackuptest_log2.bak' WITH  FILE = 1,  NOUNLOAD, STATS = 5

GO

USE [LOGBACKUPTEST]
select * from dbo.users;

But my boss wants low RTO and RPO

So how do we deal with situations where we cannot lose more than 5 minutes of data and have to be back up from a disaster in 15 minutes?

This is often done by implementing one or more secondary servers hosting the same database. All data from the primary server is replicated in real-time to the secondary servers. One example of such a mechanism in SQL Server would be Availability Groups.

However, this is where you have to understand the difference. Suppose you have one primary server and two secondary servers, and you sleep well knowing there is no chance on earth for all three servers to break down simultaneously. And since you have synchronous replication enabled, you are sure that the data is consistently replicated on all three servers with no delay. If the primary server goes down, all its data will be on the remaining servers.

You have synchronous replication, and all data is synchronised in real-time.

You have synchronous replication, and all data is synchronised in real-time.

You have synchronous replication, and ALL DATA is synchronised in real-time.

Do you see where the problem is?

If someone forgets the WHERE clause and runs a DELETE statement on the primary server, this will also remove the data from ALL replicas, in real-time, before you even realise it.

The only way to recover from such an event is to restore from a backup which may still get you in trouble for breaching RTO.

Remember that secondary replicas protect against hardware failures and not against data modification. It is critical to have change windows and test and review all changes going into production databases to avoid unwanted data modifications. Don’t give anyone direct write access to the database you care about. Only applications and interfaces are allowed. No humans. No, seriously. This is where the DBA saying “No” is perfectly justified.

Anyway, summary

You can run the Simple Recovery Model and take one backup a day if you are either ready to lose all the data since the last backup or you have a way of getting it back and do not care how long it will take.

You must run the Full Recovery Model if you care about your data and if you do, TAKE THE LOG BACKUP EVERY FEW MINUTES!

Help us grow, please share this content!

Author

Posted by
Marcin Gminski
November 15, 2021

Let us help you. Get in touch.

Help us grow, please share this content!