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

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.

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.

StackExchange Database Administrators Q#123969, answer score: 7