Competition: What’s your favorite Redgate tool? Enter now.

Converted native backup and 3rd party apps

mrclodmrclod Posts: 60
edited November 5, 2010 11:26AM in SQL Backup Previous Versions
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?

Comments

  • peteypetey Posts: 2,358 New member
    Are you able to restore the converted files using SQL Server?

    Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • 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?
  • peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • 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"'
  • peteypetey Posts: 2,358 New member
    Try the following backups using SQL Server, and see if either of those 2 products are able to read the backup files:
    BACKUP DATABASE &#91;model&#93;  TO DISK = 'c:\model_test_full.bak' WITH BUFFERCOUNT = 6, BLOCKSIZE = 65536, MAXTRANSFERSIZE = 1048576
    
    BACKUP LOG &#91;model&#93;  TO DISK = 'c:\model_test_log.bak' WITH BUFFERCOUNT = 6, BLOCKSIZE = 65536, MAXTRANSFERSIZE = 1048576
    
    Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • 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?
  • peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • 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?
  • peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
Sign In or Register to comment.