Converted native backup and 3rd party apps

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

  petey November 04, 2010 23:04

  - 0 +
• 
  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?

  mrclod November 04, 2010 23:06

  - 0 +
• 
  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.

  petey November 04, 2010 23:11

  - 0 +
• 
  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"'

  mrclod November 04, 2010 23:21

  - 0 +
• 
  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.

  petey November 04, 2010 23:28

  - 0 +
• 
  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?

  mrclod November 04, 2010 23:56

  - 0 +
• 

  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.

  petey November 05, 2010 01:10

  - 0 +
• 
  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?

  mrclod November 05, 2010 08:15

  - 0 +
• 
  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.

  petey November 05, 2010 11:26

  - 0 +

Add comment

Please sign in to leave a comment.