Activity overview
Latest activity by jgoemaat
OK, I fixed this myself after a lot of research & work.
Basically, SQL Backup still uses a V3.0 SQL Server CE format (deprecated in 2013 by Microsoft) to store backup and restore history and some other tables.
Trying to delete the troublesome record from SQL Server as obviously not working. Who knows what was triggering the division by zero error ... possibly a bug in V3.0 of SQL Server CE - there have been a lot of patches out since then?
Here are the rough steps I followed in case it helps anyone else out. My next step would have been to uninstall and reinstall (yes, pretty sad).
* stopped SQLBackup service and disabled SQL Agent log backup job
* used Sysinternals ProcessExplorer (looking for "data.sdf") to make sure it was no longer being accessed
* created a backup copy of "C:ProgramDataRed GateSQL BackupData(local)data.sdf"
* copied "C:ProgramDataRed GateSQL BackupData(local)data.sdf" to my PC running Windows 10 (I could not get any of the SQL Server CE tools to work on several server OS machine, including the problem server)
* used CompactView V1.4.12.0 (https://sourceforge.net/projects/compactview/) to data.sdf and ran the delete stabement "delete from restorehistory" (it deleted the 1 record). NOTE: there were a plethora of 3rd party tools (http://erikej.blogspot.com/2009/04/sql- ... tools.html), most did not work on the deprecated V3.0 SQL Server CE database.
* copied from my workstation back over the original "C:ProgramDataRed GateSQL BackupData(local)data.sdf"
* started SQLBackup service
* launched the SQLBackup app to make sure it works now (it did)
* enabled SQL Agent log backup job
* made sure SQLBackup app showed logs for jobs OK now since the update (it did) [image]
Hope this helps someone! / comments
OK, I fixed this myself after a lot of research & work.
Basically, SQL Backup still uses a V3.0 SQL Server CE format (deprecated in 2013 by Microsoft) to store backup and restore history and some o...
Thanks for the quick reply, Dan!
I ran your script, but it also generated the same error as when I tried to delete that restorehistory record manually: Msg 1, Level 16, State 1, Line 0
Floating point division by zero
But it did delete some backup records - here is output from running it: [image]
Therefore, my log backups are still getting the same warnings error every time.
Any more ideas on how I can fix this?
Thanks! / comments
Thanks for the quick reply, Dan!
I ran your script, but it also generated the same error as when I tried to delete that restorehistory record manually:Msg 1, Level 16, State 1, Line 0
Floating poin...
Error 166 Failed to delete old entries in local history
I just received the following warning during backups:
2/2/2017 7:03:12 PM: Warning 166: Failed to delete old entries in local history tables: Error running ExecFast command: DELETE FROM restorehist...
I have received that same error very sporadically over the past months - usually once every month or so ... doing another backup and restore works fine. Is this basically some sort of corruption in the SQB file?
It's a big pain because this leaves my database in "Loading" and sometimes "Loading/Suspect" status, which I have not been able to get the database out of, without re-creating.
Is there a backup verify command I can at least run before I retore a bad full backup, so my database isn't left in non-recoverable "Loading" state?
Thanks! 2/21/2006 6:35:02 AM: SQL Backup 3.1.0, (c) Red Gate Software Ltd 2004 - 2005
2/21/2006 6:35:02 AM:
2/21/2006 6:35:02 AM: Restoring database Tango_Reporting from "H:\backup\TangoPart1.sqb"
2/21/2006 6:35:02 AM:
2/21/2006 6:35:02 AM: RESTORE DATABASE [Tango_Reporting] FROM DISK = 'H:\backup\TangoPart1.sqb', DISK = 'H:\backup\TangoPart2.sqb' WITH RECOVERY, MOVE 'Tango_Data' TO 'F:\SQLDATA\Tango_Reporting_Data.mdf', MOVE 'Tango_Log' TO 'F:\SQLDATA\Tango_Reporting_Log.ldf', REPLACE, LOGTO = 'C:\Apps\SQLBackup', PASSWORD = 'XXXXXXXXXX'
2/21/2006 6:35:02 AM:
2/21/2006 6:35:02 AM: osql -S TANGO2 -Q "RESTORE DATABASE [Tango_Reporting] FROM VIRTUAL_DEVICE = 'SQLBACKUP_151322461', VIRTUAL_DEVICE = 'SQLBACKUP_15132246101' WITH BLOCKSIZE = 65536, MAXTRANSFERSIZE = 1048576 , RECOVERY, MOVE 'Tango_Data' TO 'F:\SQLDATA\Tango_Reporting_Data.mdf', MOVE 'Tango_Log' TO 'F:\SQLDATA\Tango_Reporting_Log.ldf', REPLACE" -b -w 120 -U sa -P XXXXXXXXXX
Msg 3242, Level 16, State 2, Server TANGO2, Line 1
The file on device 'SQLBACKUP_15132246101' is not a valid Microsoft Tape Format backup set.
Msg 3013, Level 16, State 1, Server TANGO2, Line 1
RESTORE DATABASE is terminating abnormally.
/ comments
I have received that same error very sporadically over the past months - usually once every month or so ... doing another backup and restore works fine. Is this basically some sort of corruption i...
Just to follow-up, setting the database to single_user mode then immediately back to multi_user mode seemed to fix the issue for now. Thanks again! / comments
Just to follow-up, setting the database to single_user mode then immediately back to multi_user mode seemed to fix the issue for now. Thanks again!
Thanks for the quick reply!
I think your idea of to set the database to single_user mode then set the connection back to multi_user and run SQL Backup right away before any other user connects may work.
Originally I was not using SINGLE_USER I was using RESTRICTED_USER meaning "only members of the db_owner, dbcreator, or sysadmin roles can use the database" - and got the same error. I wasn't aware two connections were used for the restore, so what you're saying makes sense.
The database restore that works fine runs on our redundant server, so no-one is using that and I don't run any ALTER DATABASE commands. The database restore that is erroring is our "reporting SQL server" that numerous users query against during the day. Since I'm doing the restore at 5:00AM, I figured no connections were open, but perhaps someone is leaving a connection open.
I'll give your great idea a try and let you know how it goes - thansk again! / comments
Thanks for the quick reply!
I think your idea of to set the database to single_user mode then set the connection back to multi_user and run SQL Backup right away before any other user connects may ...
Exclusive access could not be obtained (database in use?)
Hello! Great product so far - just having issues with a restore on one of our servers. I just installed the same, new version (3.1.0) of SQL Backup on 3 of our servers. We backup from one and re...