Backup Threads

Comments

• petey Redgate › Posts: 2,358 New member

  June 29, 2014 9:43PM

  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

  0 · Share on Twitter
• DatabaseMonster Posts: 3

  June 30, 2014 3:31AM

  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

  0 · Share on Twitter
• petey Redgate › Posts: 2,358 New member

  June 30, 2014 4:27AM

  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

  0 · Share on Twitter