Is command BACKUP asynchronous operation

Question

I got strange delays when backed up a group of databases (20 more) in one scope (TSQL) to the same file location.

Is it possible that multiple backup operations are running at the same time? I have not found any "WAIT" operation in a BACKUP clause, but this option is present in SMO command (Wait) of Backup class.

Here is my partial code (full code works well, so I want to describe it as shown below):

WHILE 1 = 1
BEGIN
   SET @currentDbName = ...

   SET @sql = 'BACKUP DATABASE ' + @currentDbName + '...'

   EXEC(@sql)
   -- maybe it is a good place to wait?
   -- WAITFOR
END

Answer 1

If you are running your backups as per the posted example code, they will not overlap.

An EXEC() statement will complete before the next statement is executed, which you can demonstrate easily:

DECLARE @SQL VARCHAR(8000);

SET @SQL = 'WAITFOR DELAY ''00:00:10:00''';

SELECT GETDATE() AS [Start]
EXEC (@SQL);
SELECT GETDATE() AS [End]

BACKUP, as with all TSQL commands, is executed synchronously. Each statement in a SQL batch will complete in turn before executing the next, whether its executed standalone or via EXEC() as in your example.

Backup using SMO is different and unrelated to the TSQL BACKUP command. With SMO you have the option of executing a synchronous backup with Backup.SqlBackup or an asynchronous backup with Backup.SqlBackupAsync.

Answer 2

I'm assuming Microsoft SQL Server here based on the sample code. There is no need to include a WAITFOR statement in the middle of the process. Also you don't need to use dynamic SQL. The BACKUP DATABASE statement accepts variables for the database name.

When you are seeing the delays what is the wait type of the spid running the backup?