User profile for kenneyh

Format SQL fails when using INSERT INTO ... EXECUTE

I'm currently running SQL Prompt 8.0.8.2086 but have been having this problem for many weeks, also on previous versions, until I finally figured out what was triggering it yesterday. I've had a set...

2 followers 2 comments 0 votes

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: Open SQL Backup 7 GUI Select the instance I want to restore to from the list of instances Click on the "Restore..." button Leave option at "Select from backup history" and choose database from the Database dropdown list Select the restore point immediately following the time and date that I want to restore to Click on Next 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 Click on Next Leave selection on "Overwrite existing database" and select "Kill any existing connections to the database" Click on Next Select "Discard transactions in the tail of the transaction log (WITH REPLACE)" Click on Next Leave Recovery completion state on "Operational (RESTORE WITH RECOVERY)" Select "Restore transaction log to point in time" and set the desired time Unselect "Check for orphaned users and list in log file" since I don't need this check for most of my restores Click on Next 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. 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 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. / comments

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 befor...

0 votes

Peter, Thank you for the quick turn-around on a fix. I finally got a chance to try it out and it appears to be working properly. I'll reply back if it displays any unwanted behaviors. I see what you mean about reporting errors. I like your workaround for the issue. I'm using Ola Hallengren's maintenance scripts and I'll have to weigh out the benefits of getting an error back when no files are deleted compared to the overhead of modifying his code every time there is an update. / comments

Peter, Thank you for the quick turn-around on a fix. I finally got a chance to try it out and it appears to be working properly. I'll reply back if it displays any unwanted behaviors. I see what ...

0 votes

Backups are not deleting when using encryption

I've recently noticed that my backups have not been deleting at all, which has caused multiple space issues (which is how I noticed the problem). At first I thought maybe it was because I had chan...

2 followers 2 comments 0 votes

How can we help you today?

kenneyh

Activity overview