Backup Threads

DatabaseMonster

Hello,

Can anyone tell me if there is a programmatic way of determining how many threads a database backup used as part of the sql backup?

petey

For backup reader threads, it's always the number of physical devices your database files are stored on (see http://www.sqlbackuprestore.com/backupr ... writes.htm).

For SQL Backup writer threads, it depends. If you are backing up to a single file without the THREADCOUNT parameter, there's only 2 threads - 1 to read the backup data provided by SQL Server and compress/encrypt it, and another to write the processed data.

If you are backing up to multiple files, it's 2 x the number of files. If you are backing up to a single file using a THREADCOUNT value of 2 or more, it's the THREADCOUNT value + 1.

DatabaseMonster

Thanks for the info!

So if I backup a database using 4 x threads to a single file is there a way then to query the backup information in code so that when I come to restore the database I know how many DISK keywords to specific

petey

You could use the RESTORE SQBHEADERONLY command e.g.

EXEC master..sqlbackup '-sql "RESTORE SQBHEADERONLY FROM DISK = [g:\backups\AdventureWorks.sqb]"'

For backups that were split across multiple files, you will see a value for 'File number' e.g. 1 of 3, 2 of 3 etc.

For backups that used multiple threads but written to a single file, you will see a value for 'Threads' e.g. 5 (multi-threaded file).