NOTE: As of Dec 2020, the information in this blog post should be considered out-of-date, and provided for historical reference only.

Overview

Many a time, we are asked by customers about the effect of overlapping backup schedules: for example, whether they can backup the transaction log at the same time that a long-running complete backup of the database is taking place.  Books Online does talk about the concurrency, but does not cover all versions or combinations.

Here is a short test case to illustrate the concepts involved. At the end of the post, I have provided a summary of what works in what version.

Test Case

I ran the 3 types of backups on a SQL 2005 / 2008 database at the same time:

• COMPLETE a.k.a. FULL (backup database mydb to disk = ‘c:tempmydb.bak’ with init)
• DIFFERENTIAL (backup database mydb to disk = ‘c:tempmydb_diff.bak’ with differential, init)
• LOG (backup log mydb to disk = ‘c:tempmydb.trn’ with init)

Here is the output of each command for verbose reference. Also included immediately below is a snapshot from sysprocesses showing the blocking of the DIFFERENTIAL backup and the concurrent execution of the LOG backup.

spid	blocked	waittype	lastwaittype	waitresource	cmd
61	0	0x0062	ASYNC_IO_COMPLETION		BACKUP DATABASE
61	0	0x0086	BACKUPBUFFER		BACKUP DATABASE
61	0	0x0087	BACKUPIO		BACKUP DATABASE
62	61	0x0004	LCK_M_U	DB: 16 [BULKOP_BACKUP_DB]	BACKUP DATABASE (this is the differential backup)
68	0	0x0087	BACKUPIO		BACKUP LOG
68	0	0x0087	BACKUPIO		BACKUP LOG

Output from FULL backup

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.

Processed 17512 pages for database ‘mydb’, file ‘mydbData’ on file 1.

Processed 1 pages for database ‘mydb’, file ‘mydbLog’ on file 1.

BACKUP DATABASE successfully processed 17513 pages in 18.986 seconds (7.206 MB/sec).

SQL Server Execution Times:

CPU time = 70 ms,  elapsed time = 20256 ms.

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.

Output from DIFFERENTIAL backup

SQL Server parse and compile time:

   CPU time = 0 ms, elapsed time = 0 ms.

Processed 32 pages for database ‘mydb’, file ‘mydbData’ on file 1.

Processed 1 pages for database ‘mydb’, file ‘mydbLog’ on file 1.

BACKUP DATABASE WITH DIFFERENTIAL successfully processed 33 pages in 0.321 seconds (0.801 MB/sec). <– actual time for the differential backup to complete

SQL Server Execution Times:

CPU time = 51 ms,  elapsed time = 19777 ms.   <– total time to execute the command, including wait and runnable time

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.

Note that the time taken by the command is just 0.321 seconds but the elapsed time is 19.777 seconds, which clearly demonstrates the blocking.

Output from LOG backup

SQL Server parse and compile time:

   CPU time = 0 ms, elapsed time = 0 ms.

Processed 3 pages for database ‘mydb’, file ‘mydbLog’ on file 1.

BACKUP LOG successfully processed 3 pages in 7.958 seconds (0.002 MB/sec).

SQL Server Execution Times:

CPU time = 60 ms,  elapsed time = 15106 ms.

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.

Time taken by command is around 52% of time elapsed, which is probably due to the fact that we are on a single-CPU system and the task had to wait for runnable time on the CPU.

Summary

Here is the summary of the testing I conducted. I hope the summary is useful to all of you.

	What is requested?
What is already running? (rows below)	Complete	Differential	Log
Complete backup is running	Blocks in all versions *	Blocked in all versions *	Concurrently allowed in SQL 2005 / 2008. Not allowed in 2000.
Differential backup is running	Blocks in all versions *	Blocked in all versions *	Concurrently allowed in SQL 2005 / 2008. Not allowed in 2000.
Log backup already is running	Concurrently allowed in SQL 2005 / 2008.	Concurrently allowed in SQL 2005 / 2008.	Blocked in all versions *

* Note: wait type of the blocked process will be LCK_M_U with a waittype of BULKOP_BACKUP_DB