What is the use of restore headeronly when performing a backup?

Question

When I monitor my backups with the following query:

SELECT      command, percent_complete,
            'elapsed' = total_elapsed_time / 60000.0,
            'remaining' = estimated_completion_time / 60000.0
FROM        sys.dm_exec_requests
WHERE       command like 'BACKUP%'
or command like 'RESTORE%'

I notice that before the backup, SQL Server perform a restore headeronly then the backup.

I was wondering what was the use for it and if its execution time could be reduced somehow. It appears to take longer than the actual backup.

Answer 1

I notice that before the backup, SQL Server perform a restore headeronly then the backup.

This might be part of restore steps that are performed internally by sql server.

You can look into the restore phases using DBCC TRACEON(3604, 3605, 3004);. Use it only for educational purpose on a NON PROD server.

As you can see above that there are essentially 3 main phases of restore - data copy, undo and redo phase along with other sub-phases where in it opens and loads the backup set as well.

It appears to take longer than the actual backup.

To improve restore time, you should

• Enable Instant file initialization.
• Use T-SQL and restore with REPLACE for an existing database instead of dropping and restoring.
• Make sure you do proper maintenance of msdb.
• Check for VLFs. The more VLFs, the longer restore time.

Refer to Babysitting a slow backup or restore by @AaronBertrand.

Answer 2

RESTORE HEADERONLY allows SQL Server to check the target file for certain properties ahead of the actual backup.

You can prevent this by running the backups using the T-SQL BACKUP DATABASE ... in a SQL Server Agent job instead of using the Maintenance Plan GUI, which is rife with problems.

Answer 3

RESTORE HEADERONLY can take a long time if your backup contains a lot of Virtual Log Files ( VLFs ). You might have some success in addressing the length of time it takes to perform restores if you shrink and regrow the source databases log file to an appropriate size.