Bug in Backup 7.7.0.18

grumpydba

Restoring via the GUI

The GUI seems to be confused about copy only backups.

If you have a backup chain like this.

Wed - Diff
Tue - Full copy
Mon - full

and choose to select the Wednesday diff backup, the gui will try and restore the Tuesday copy only and then the Wednesday diff.
Obviously that doesn't work!

Anu D

Thanks for your post!

What option are you using to select/add the backup files? Is it "Select from backup history" or "Browse for backup files to restore"?

In "Select from Backup history" option: SQL Backup Pro retrieves the files for the selected backup. If you selected a transaction log backup or a differential backup from a backup set, SQL Backup Pro automatically retrieves files for all backups in the set. For example, if you selected a transaction log backup, SQL Backup Pro retrieves all transaction log backups since the corresponding full backup, and the full backup itself. You may see displayed while SQL Backup Pro searches for the appropriate files.

In "Browse for backup files to restore" SQL Backup will tell you if any file is missing or not right to be restored.

More about it here in this article: http://documentation.red-gate.com/displ ... ct+backups

Please let me know the exact steps you followed.

kenneyh

I'm experiencing this same problem, and it is a bug. And here is why.

SQL Backup Pro will stop at the first full backup that it finds (or to be more precise, the last full back that occurred before your requested restore point) regardless of whether or not it is part of the backup chain. If you use COPY_ONLY backups for ad-hoc backups or for archival backups (like I do for weekly and monthly backups for archival purposes) or any other special case then it creates a problem for you if the COPY_ONLY full backup occurred after the normal full backup because SQL Backup Pro will select the most recent full backup since the selected restore point regardless of the Copy Only status (which is recorded in the is_copy_only column of the backupset table) then the selects the most recent differential since the full backup and before the restore point, if one exists, then the transaction log backups since the differential (or full backup if no differential) up to and including the restore point. I assume that the same would be true for Copy Only transaction backups as well, but I don't use them and have not tested SQL Backup Pro's handling of them so I can't say for certain that those would be included as well and cause your restore to fail on the transaction log backup following the Copy Only transaction log backup.

The Copy Only backups should be included in the list of restore points that you select from when you use the "Select from backup history" option on the "Step 1 of 4" screen for your restore as restoring from a Copy Only backup is a very valid option. But the process that populates the next screen, "Step 1 of 4 (continued)", should filter out backup files that are Copy Only, except for the last file as that can be restored from, from its selection process as you can't restore any files after a Copy Only file is restored from.

Here is an excerpt from the documentation linked by Anu:
"When you click Next, SQL Backup Pro retrieves the files for the selected backup. If you selected a transaction log backup or a differential backup from a backup set, SQL Backup Pro automatically retrieves files for all backups in the set. For example, if you selected a transaction log backup, SQL Backup Pro retrieves all transaction log backups since the corresponding full backup, and the full backup itself. You may see displayed while SQL Backup Pro searches for the appropriate files."

For the most part this is what the restore process does, but as I've noted above it fails to exclude Copy Only backups where appropriate to do so (which is in all cases except when the Copy Only backup is the last or only backup file to be restored).

Since Anu will probably request the exact steps that I follow to restore a database I will include that here:

1. Open SQL Backup 7 GUI
2. Select the instance I want to restore to from the list of instances
3. Click on the "Restore..." button
4. Leave option at "Select from backup history" and choose database from the Database dropdown list
5. Select the restore point immediately following the time and date that I want to restore to
6. Click on Next
7. Since I use encrypted backups I supply the password and click on Decrypt
   • If I expand the "File name" column enough on this screen (which is hard to do since the window is not resizable) I can see that it selected a Copy Only backup file for the full to be restored and there is no way to change the file used on this screen
8. Click on Next
9. Leave selection on "Overwrite existing database" and select "Kill any existing connections to the database"
10. Click on Next
11. Select "Discard transactions in the tail of the transaction log (WITH REPLACE)"
12. Click on Next
13. Leave Recovery completion state on "Operational (RESTORE WITH RECOVERY)"
14. Select "Restore transaction log to point in time" and set the desired time
15. Unselect "Check for orphaned users and list in log file" since I don't need this check for most of my restores
16. Click on Next
17. Click on Finish

The restore will start and the full backup will restore successfully but the next backup file (the differential in most cases for me) will fail these or similar errors:

9/21/2015 8:23:29 AM: Thread 0 error: 
Process terminated unexpectedly. Error code: -2139684860 (An abort request is preventing anything except termination actions.)
9/21/2015 8:23:29 AM: 
9/21/2015 8:23:29 AM: SQL error 3013: SQL error 3013: RESTORE DATABASE is terminating abnormally.
9/21/2015 8:23:29 AM: SQL error 3136: SQL error 3136: This differential backup cannot be restored because the database has not been restored to the correct earlier state.

I know of 2 workarounds for this issue.

1. Restore your full backup separately using the "Non-operational (RESTORE WITH NORECOVERY)" option (line item 13 above) then do the restore again as I listed out above except you deselect the Copy Only full backup file, between steps 7 and 8 above
2. Do the restore as I listed out above but instead of doing step 17 (Click on Finish) you would click on the Script tab on that screen and copy the code (I use the "Extended stored procedure" option) from that screen and paste it into SSMS and then replace the full backup file name and location with the correct file and location and run it from there

Both methods take about the same amount of effort so it's really a personal preference.

I thought of making a script that would make the appropriate restore scripts for me (and maybe even run them as well) but I don't do restores often enough for me to spend the time on the project especially since SQL Backup Pro should do this correctly on its own and it is a commercial product and not freeware so I shouldn't have to do workarounds or make my own restore script generator.