SQL Server Backups and Transaction Log Questions

By: Sergey Gigoyan | Comments (2) | Related: > Backup

Problem

In this tip we will continue to investigate some questions related to SQL Server backup and restore. In the previous tip (Solve Common SQL Server Restore Issues) we discussed questions about the transaction log chain, restoring from COPY_ONLY backups and backup to 'NUL' disk. Now we will address questions on how to run Full and Transaction log backups in parallel, will illustrate the difference between striped and mirrored backups, and discuss issues related to truncation of the transaction log file after a transaction log backup.

Solution

Now let's formulate each of these questions and explain them in detail.

Can we start a SQL Server transaction log backup when a Full or Differential backup is running?

Let's understand this with an example. Suppose, we have the TestDB database (make sure that your TestDB database is only used for testing purposes, to avoid damaging important data during testing process). Now we'll open two query windows and run the full backup and transaction log backups in parallel. First we run the full backup in the first query window with the following code:

--Query 1
--Tested on SQL Server 2014

USE master
GO

PRINT 'Full Backup start time:'
PRINT (CONVERT( VARCHAR(24), GETDATE(), 121)) 

--Backing up the database
BACKUP DATABASE TestDB 
TO DISK = 'E:\Backup\TestDBFull.bak'

PRINT 'Full Backup completion time:'
PRINT (CONVERT( VARCHAR(24), GETDATE(), 121))

And after that, while the full database backup is in progress, we will start the transaction log backup in the second window:

--Query 2
--Tested on SQL Server 2014

USE master
GO

PRINT 'Log Backup start time:'
PRINT (CONVERT( VARCHAR(24), GETDATE(), 121)) 

--Backing up the transaction log
BACKUP LOG TestDB 
TO DISK = 'E:\Backup\TestDBLog.trn'

PRINT 'Log Backup completion time:'
PRINT (CONVERT( VARCHAR(24), GETDATE(), 121))

As we see from the results in the screen shot below the transaction log backup started after the beginning of full backup, and finished before the full backup finished:

So, it is possible to run a transaction log backup while the full backup is running. Also, when a differential backup is running we can start a transaction log backup. This is useful when our database full or differential backup requires a long time to complete and we want to minimize data loss in case of database file corruption during the full\differential backup process.

What happens when we specify more than one disk in the SQL Server backup command?

We can specify more than one disk when we backup our SQL Server database using T-SQL commands or SQL Server Management Studio. Here is some sample T-SQL code:

USE master
GO

BACKUP DATABASE TestDB TO
 DISK = N'C:\Backup\TestDB1.bak',
 DISK = N'E:\Backup\TestDB2.bak'
GO

The screen shot below shows how to create multiple backup files on different disks in SQL Server Management Studio by using the "Add..." button.

It is important to understand that with this technique we are striping database backup files to different paths and not mirroring the database backup. Each of these files are part of the whole backup. All these files are needed when we perform the database restore. If we try to restore the database using only one of the files we will receive the following error as shown in the screen shot below:

USE master
GO

RESTORE DATABASE TestDB FROM DISK = N'C:\Backup\TestDB1.bak'
WITH REPLACE
GO

When we use all of the striped backup files for the restore, this allows us to successfully restore the database as shown below:

USE master
GO

RESTORE DATABASE TestDB FROM DISK = N'C:\Backup\TestDB1.bak', DISK =N'E:\Backup\TestDB2.bak'
WITH REPLACE
GO

Mirroring SQL Server Backups

Mirroring SQL Server backup files to different locations is also possible. In this case we must specify the 'MIRROR TO' clause:

USE master
GO

BACKUP DATABASE TestDB TO
DISK = N'C:\Backup\TestDB1.bak'
MIRROR TO DISK = N'E:\Backup\TestDB2.bak'
WITH FORMAT, INIT
GO

We can use each of these backups to restore the database:

USE master
GO

RESTORE DATABASE TestDB FROM DISK = N'E:\Backup\TestDB2.bak'
WITH REPLACE
GO

Mirroring SQL Server Striped Backups

It is also possible to mirror striped backups:

USE master
GO

BACKUP DATABASE TestDB
TO DISK = N'C:\Backup\TestDB1.bak',
   DISK = N'C:\Backup\TestDB2.bak'
MIRROR 
TO DISK = N'E:\Backup\TestDB3.bak',
   DISK = N'E:\Backup\TestDB4.bak'
WITH FORMAT
GO

In this case we can restore the database either using the first group of backup files or the second. Below we are restoring the database using the first two striped backup files:

USE master
GO

RESTORE DATABASE TestDB FROM DISK = N'C:\Backup\TestDB1.bak', DISK =N'C:\Backup\TestDB2.bak'
WITH REPLACE
GO

This also works using the second set:

USE master
GO

RESTORE DATABASE TestDB FROM DISK = N'E:\Backup\TestDB3.bak', DISK =N'E:\Backup\TestDB4.bak'
WITH REPLACE
GO

These examples are essential to not confuse striped and mirrored SQL Server backups.

Is it true that after a SQL Server transaction log backup the transaction log file is truncated immediately?

No, not always. There are some instances which can delay the SQL Server transaction log file truncation. After a transaction log backup (if the log backed up normally, without COPY_ONLY) SQL Server usually truncates the transaction log file, which means that the inactive portion of log file can be reused not that any disk space is freed. However, there are some cases which prevent SQL Server from truncating the log file after a log backup. In the following cases the transaction log file cannot be truncated even after the log backup:

When a data backup operation is running. During the data backup operation, the transaction log is needed, so it cannot be truncated.
When long-running active transactions exist.
When the database participates in mirroring, each transaction log must remain active until it is written to the mirrored server's disk. So, in this case the transaction log cannot be truncated.
When database acts as a publisher in transactional replication, and all transactions related to publications, have not been delivered to the distribution database yet.

Next Steps

Check out these resources:

About the author

Sergey Gigoyan is a database professional with more than 10 years of experience, with a focus on database design, development, performance tuning, optimization, high availability, BI and DW design.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips