Backup Threads

• 
  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.

  petey June 29, 2014 21:43

  - 0 +
• 
  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

  DatabaseMonster June 30, 2014 03:31

  - 0 +
• 
  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).

  petey June 30, 2014 04:27

  - 0 +

Add comment

Please sign in to leave a comment.