January 20, 2006 at 9:02 am
Hello All,
I am a default SQL Server DBA and have limited knowledge of the product. We have GIS (Geographic Information System) software that uses SQL Server as it's backend.
Setup: SQL Server 7.0, Database Recovery : Full, Full Backups weekly, Diff. Backups Daily, Tran Log Backups every two hours during business day.
Situation: I need to restore to 1/8/06. I cannot restore to a point in time as the interface won't allow me to choose any other day than today. If I try simply to restore (by selecting these dates as a start time and "unchecking" all susequent dates) to 1/1/06 or 1/8/06 I receive an error (Microsoft SQL - DMO- ODBC SQLState: HYOOO, Location: recbase.cpp: 1375, Expression: m_offBeginVar < m_SizeRec, SPID: 51, ProcessID: 924)
To probably complicate things I just recently deleted my backup of old transaction logs...it had grown to 220GB (I assume because I had it set to "append" rather than overwrite) and have only one trans log back up since then.
I need big help here....PLEASE tell me all is not lost.
Thank you,
Lonnie Meinke
January 20, 2006 at 9:23 am
Hi Lonnie,
What's the date of the full database backup immediately preceding 01/08/2006? You have to start your restores with a full backup then restore differential backups and log backups.
Greg
Greg
January 20, 2006 at 9:33 am
Hi Greg,
Thanks for the response. The last full backup is one week prior or 1/1/06. I have tried to restore this but get the same error message. Am I possibly making an error in the restore process? When I restore I am selecting the starting date and then "unchecking" all subsequent restore dates....is this how one would restore to a particular backup?
I even tried to create a new database and restore into it from that date with no luck.
Because I have deleted the old transaction log backups I assume I am limited to restoring full and differential backups only. My actual target date to restore to would be 1/10/06. This also failed so I was trying to simplify life and just choose the last full backup prior to that (1/8/06).
Thanks again,
Lonnie
January 20, 2006 at 10:18 am
Lonnie,
I wonder if using backup "from device" could help you here. Grab a full back up copy (notaa differential), place it in a directory (any one is fine), rename the backup to anything.BAK, and ...
Start again the restore, but this time don't select any of the backups shown instead select From Devices.
Follow the dialog boxes until you get to the location you saved the renamed copy, select that file and just answer OK to all until you get back to the backup box, go to Options and check the FORCE Restore over existing database, the underneath box should be OK because you are using a back up from the original database. Hope this helps
January 20, 2006 at 10:27 am
It sounds like you're using Enterprise Manager to do the restore. In the 'Restore database' popup, make sure you choose the correct backup device/file in the dropdown called 'First backup to restore:'. This should show all the backup sets in that device. By default the first full backup set and the latest differential and log backups will be checked.
Check the full backup for 01/08/2006 and the differential for either 01/09/2006 or 01/10/2006 depending on which one occurred before the failure you're recovering from.
Greg
Greg
January 20, 2006 at 11:55 am
Hello All,
Thank you again for all the suggestions (great resource). I wish I could point to a particular action that enabled me to restore to 1/8/06, but on the fourth try, using the same interface all went well.
Thank you for all your help
January 20, 2006 at 1:15 pm
Lonnie,
It almost sounds like you are using the vendor's product to interface and administer the database, is that true? If so, we really aren't going to be able to help you with that...it's an issue for your vendor.
However, if you are using Enterprise Manager or Query Analyzer, that's where we can help.
Suspecting that you are using the vendor's interface, I suggest you start learning to use Enterprise Manager and Query Analyzer. The Books OnLine that come with SQL Server are a great learning tool.
You should have been able to use Query Analyzer and run:
RESTORE DATABASE dbname
FROM backup_device_name
WITH NORECOVERY
RESTORE LOG dbname
FROM backup_device_name
WITH STOPAT '2006.......',
RECOVERY
-SQLBill
January 20, 2006 at 1:26 pm
SQLBill,
I am using Enterprise Manager to run all commands. Thanks again for everyone's advice it was very much appreciated.
Lonnie
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply