Backup Threads

DatabaseMonsterDatabaseMonster Posts: 3
edited June 30, 2014 4:27AM in SQL Backup Previous Versions
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?

Comments

  • peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • 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
  • peteypetey Posts: 2,358 New member
    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).
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
Sign In or Register to comment.