Jump to content
sergei.sokolov

Problems with transferring SQL database backup sets

Recommended Posts

Dear all,

 

Can anyone please help with identifying the root of the problem with transferring SQL database backup sets...

 

At first, SQL databases are being backed up to a disk drive without any errors, then the backup sets get transferred to a tape library.

 

At some point we started to notice the following messages for some SQL databases:

 

"Can't transfer intermediate databases for [databasename], [timestamp], database backup history unavailable"

 

It doesn't look like the media is corrupted.

 

What could this message mean exactly and is there any way to troubleshoot it otherwise than recreating all the sets from scratch?

 

 

Thanks in advance,

Sergei

  • Like 1

Share this post


Link to post
Share on other sites

Uncheck the script option to transfer intermediate databases

 

Are you doing full backups of SQL or incremental?.

Share this post


Link to post
Share on other sites

Thank you Robin,

 

We'll try that option.

 

For SQL we are doing both Full and Log backups.

 

What would you suggest - do we have to recreate or transfer again the backups that we had those error messages for?

We just need to ensure that in case something bad happens, we can restore the databases. :)

 

 

Regards,

Sergei

Share this post


Link to post
Share on other sites

The error is telling you that the logs may have been transfered but the Full Backup probably exists on a different backup set.

 

If you need to be safe, you must transfer the full and all log backups.

Share this post


Link to post
Share on other sites

Robin,

 

What do you mean by "transfer the full and all log backups"? The options for Transfer backup Set are pretty limited. All I can really do is select the source backup set and destination set. My assumption is that it tries to transfer all databases, logs, snapshots, and whatever else is in the source. I also get the same error if use the "transfer intermediate databases" option.

Share this post


Link to post
Share on other sites

(using Retrospect MultiServer 8.2.0 (177))

 

I am seeing the same issue as Servei.  When I use the option "transfer intermediate databases", I also get the same error message, "Cannot Transfer intermediate Databases ... Database Backup History Unavailable".  If I turn this message off, I can transfer without errors.

 

I attempted to restore a database from my destination backup set.  I selected one of the logs for which I recieved an error.  Both backup sets, the source and destination, contain the full backup and all log backups up to the one selected.  When I chose to restore from the latest log backup on my destination set, it chosses additional log backups and the full back from the source set.  The restore completes successfully. 

 

However, if I forget my source backup set, simulating the disaster where only my destination backup set survives, and then try to restore my latest log backup, it does not select the full backup or preceeding log backups.  Consequently, it does not allow me to restore my DB backup.  This indicates the error message I am seeing is real, and not to be ignored by turning off the "transfer intermediate databases" feature.

Share this post


Link to post
Share on other sites

Robin,

I just installed Multiserver 8.5.0(136) and was able to demonstate a problem with a simple test.  I chose a very small DB, and created 2 new disk backup sets, one called Original and the other called Copy.  

 

I performed a Full backup of my DB to Original.  Then I performed a Log backup to Original.  A quick test of Restore Database shows no problems.  If I restore the log backup, it selectes both the log and the full from Original.

 

Next, I performed a Transfer Backup Sets from Original to Copy. I can restore just fine from the Full backup on Copy.  But if I try to restore from the Log backup from Copy, it will select the Full backup from Original, and the Log from Copy.  If Original weren't available, I could not restore the Log backup.  I would be out of luck, even though the full backup I need is right in the same backup set. 

 

When a backup set transfer if performed, it should switch the predicessor backup to the destination backup set instead of retaining the original backup set.  Otherwise the entire transfered backup set is useless.

Share this post


Link to post
Share on other sites

Furthermore, I think I found the cause of some of the "Can't Transfer Intermediate Databases" error.  The problem is that if you do enough backup set transfers, eventually backus in the source backup set may point to predicessors in a destination backup set.  This is the case here in this image of the Properties of one backup on the Database Backup History report.  The backup set, SQL Server 2008, is the source set for all backup set transfers.  And it is the destination for all full and log backups of the database.  However, after a backup set transfer the history now includes a log backup on another backup set.  If I try to transfer this backup set again using the Transfer Intermdiate Databases option, I will get the Can't Transfer Intermediate Databases error.  This condition is intermittent. 

 

post-24658-0-76270000-1386307851_thumb.gif

Share this post


Link to post
Share on other sites

I think because all of these databases are tracked in the Database backup History report, Retrospect is getting the different references confused.

 

If you really want to test a restore from the set created during a transfer, you may need to remove all other references to these databases from the Database backup History report, since that is used by Retrospect to identify the location for the different database parts. 

Share this post


Link to post
Share on other sites

I discovered that the confusion is caused by an alphetical dependency between the backup sets.  When a log DB set is transfered, the backup set referred to by the destination log backup will be the backup set listed first alphetically.  For instance:

 

If I first perform Full and Log backups to the backup set Original, then:

  1. When I transfer that backup set to the set Copy, the transfer works properly.  The log backup in Copy only refers to other backups in Copy.  However, now the log backups in Original also refer to backups in Copy.
  2. When I transfer the Original backup set to ZzzCopy, then the log backups in ZzzCopy refer to ZzzCopy and to Copy
  3. If I subsequently transfer the backup set from Original to NnnCopy, the log backup in NnnCopy refers to NnnCopy and to Copy.

This points to a valid work-around for the case where there is only 1 destination backup set:  Make sure that the destination backup set's name is alphetically lower than the source set.  Then the transfered set will work properly.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×