Backup Threads

Compresses, encrypts, secures and monitors SQL Server backups.

Backup Threads

Postby DatabaseMonster » Fri Jun 27, 2014 9:31 am

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?
DatabaseMonster
 
Posts: 2
Joined: Fri Jun 27, 2014 9:20 am

Postby petey » Mon Jun 30, 2014 2:43 am

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 7
petey
 
Posts: 2301
Joined: Sun Apr 24, 2005 12:34 pm

Postby DatabaseMonster » Mon Jun 30, 2014 8:31 am

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
 
Posts: 2
Joined: Fri Jun 27, 2014 9:20 am

Postby petey » Mon Jun 30, 2014 9:27 am

You could use the RESTORE SQBHEADERONLY command e.g.

Code: Select all
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 7
petey
 
Posts: 2301
Joined: Sun Apr 24, 2005 12:34 pm


Return to SQL Backup 7

Who is online

Users browsing this forum: No registered users and 1 guest