Backup Threads

Compresses, encrypts, secures and monitors SQL Server backups.

Moderators: JonathanWatts, Chris Auckland, eddie davis, Colin Millerchip, Brian Harris, james.billings, RBA, petey

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

Postby lindaquist » Wed Jul 09, 2014 6:29 am

For non technical user, it is not an easy task to repair sql database mannually, i would recommend to such people, go for third party application, which is easy and fast way to recover sql database.

Go to Google search there lot of tools available there, one such application is here: - http://www.filesrecoverytool.com/sql-da ... epair.html
lindaquist
 
Posts: 1
Joined: Wed Jul 09, 2014 6:24 am


Return to SQL Backup 7

Who is online

Users browsing this forum: No registered users and 0 guests