Converted native backup and 3rd party apps

mrclod

I recently converted some .sqb files to native backup format. That worked fine, however, I am finding 3rd party applications such as a log reader could not open the files claiming multiple errors.

Used sqb2mtf and SQBConverterGUI, but same result.

Any other ways to convert these?

petey

Are you able to restore the converted files using SQL Server?

Thanks.

mrclod

That wasn't the question.

Is there another way to convert these so that a log reader tool can be used on these files?

Appears that the files may be compliant on mtf, but not fully compliant?

petey

The point of my question was to determine if the SQL Backup files were converted correctly. If SQL Server is able to restore those files, then they have been converted correctly. If that is the case, we will need to create a couple of native SQL Server backup files, using the same backup parameters that SQL Backup uses, and check if your log reader is able to read those files. Then decide if your log reader is mtf compliant.

Thanks.

mrclod

Able to restore with converted.

So the latter appears in line. Both ApexSQL and Quest Toad claim to be MTF compliant.

Here is an example of our full backup:

EXECUTE master..sqlbackup '-SQL "BACKUP DATABASES [master,model,msdb,db_maintenance]
TO DISK = ''NEW_LOCAL:\BACKUPS\<database>\<AUTO>.sqb''
WITH ERASEFILES_ATSTART = 7,
ERASEFILES_REMOTE = 95,
FILEOPTIONS = 4,
PASSWORD = ''<ENCRYPTEDPASSWORD></ENCRYPTEDPASSWORD>'',
DISKRETRYINTERVAL = 30,
DISKRETRYCOUNT = 10,
COMPRESSION = 3,
COPYTO = ''\\copy\DatabaseBackups\<database>\'',
INIT,
KEYSIZE = 256,
THREADCOUNT = 3,
VERIFY"'


Example of our log backup:

EXECUTE master..sqlbackup '-SQL "BACKUP LOG [model]
TO DISK = ''NEW_LOCAL:\BACKUPS\<database>\<AUTO>.sqb''
WITH ERASEFILES_ATSTART = 7,
ERASEFILES_REMOTE = 35,
FILEOPTIONS = 4,
PASSWORD = ''<ENCRYPTEDPASSWORD></ENCRYPTEDPASSWORD>'',
DISKRETRYINTERVAL = 30,
DISKRETRYCOUNT = 10,
COMPRESSION = 3,
COPYTO = ''\\copy\DatabaseBackups\<database>\'',
INIT,
KEYSIZE = 256,
THREADCOUNT = 3,
VERIFY"'

petey

Try the following backups using SQL Server, and see if either of those 2 products are able to read the backup files:

BACKUP DATABASE [model]  TO DISK = 'c:\model_test_full.bak' WITH BUFFERCOUNT = 6, BLOCKSIZE = 65536, MAXTRANSFERSIZE = 1048576

BACKUP LOG [model]  TO DISK = 'c:\model_test_log.bak' WITH BUFFERCOUNT = 6, BLOCKSIZE = 65536, MAXTRANSFERSIZE = 1048576

Thanks.

mrclod

One tool worked, the other failed.

So getting somewhere with Quest Toad.

Now what is the next step to get the converted backup to work?

petey

Now what is the next step to get the converted backup to work?

When the backup was created using SQL Backup, it used the same parameters as you just did in the previous example. Only difference was that SQL Backup compressed the backup data. When converting back to native MTF format, SQL Backup simply uncompressed the backup data, and the resulting MTF file is exactly the same as you would have gotten had you used SQL Server with those parameters. It isn't possible for SQL Backup to uncompress the backup data to any other MTF format.

Best guess is that the Toad/ApexSQL expects a BLOCKSIZE of 512, not 65536 as used by SQL Backup, as 512 is the default size used by SQL Backup.

mrclod

I used the blocksize specified by your previous post, so it would appear that it can handle 65536.

Now the potential difference I do see is that after conversion, I get multiple files (multiple threads used to get the backup) and that is what may not be liked by any of the tools.

I will test that using a single thread, but is there anyway for convert to create a single file?

petey

When you back up using multiple threads in SQL Backup, it's equivalent to backing up to multiple files using native SQL Server backup syntax e.g.

BACKUP DATABASE model TO DISK = 'e:\backups\model_01.bak', DISK = 'e:\temp\model_02.bak'

The only difference is that SQL Backup combines the contents of the 2 backup devices into a single file. Thus, when converted back to MTF format, 2 files will be created. SQL Server expects the backup set to be on 2 files.