CONVERT as a seperate utility?

Adam.Bean

Does Red-Gate or any user have a utility that will CONVERT a Red-Gate SQL Backup to Native backup without having an install of SQL Backup?

This would be extremely handy in our environment to allow developers to convert our production backups into native for restores onto their own local machines without having SQL Backup installed.

Thanks

HJoyce

Yes, you can use the SQL Backup sqb2mtf command line utility, which is provided in the SQL Backup folder. You can distribute this application as required; you do not need to install SQL Backup in order to use it.

(There's also a GUI for this utility which can be downloaded from the Red Gate Labs).

Alternatively you can use the CONVERT command with the SQL Backup -SQL parameter to convert SQL Backup files (.sqb) to native SQL Server format.

For example:
master..sqlbackup '-SQL "CONVERT 'C:\Backups\pubs.sqb' TO 'C:\Backups\pubs.bak' " '

Adam.Bean

Very cool, thanks for the info.

When using that tool, why does it split the backup file in 14 smaller files when converting?

Jason Cook

If you have used the THREADCOUNT keyword, the conversion utility will split each thread out into a seperate file (since SQL Server cannot deal with multi-threaded backups).

Therefore, in your case, you would have used THREADCOUNT = 14 during the backup; which results in 14 "threads" of data (and hence 14 native files).

Hope that helps,
Jason

Adam.Bean

Are you saying it splits it up in the amount of threads that it was backed up as? You don't have the ability to change that on the convert???

Jason Cook

That is correct.

While SQL Backup provides the ability to perform multi-threaded backups, SQL Server does not have the ability to handle multi-threaded restores. When you come to convert the backup to a native backup, the only way this can be done is to split the multiple threads out, so that one thread exists in one file. Unfortunately that is the only option.

Hope that helps,
Jason

Adam.Bean

That's a bummer ...

So what is the performance increase by using multiple threads on backups? I'm wondering If I should just leave it single threaded then ...

Jason Cook

Hi,
As with any performance metrics, it will vary with every different system out there. However, given our own tests with multiprocessor systems, we have seen improvements of 50% or higher over a single thread - which will be quite common if the bottleneck was the CPU.

The one thing worth noting with the conversion is that the file names are entirely predictable... the files will each be appended with a _xx value, e.g. _01, _02 and so on. So if you know the number of threads beforehand, you will be able to compute the generated filenames afterwards.

Hope that helps,
Jason