SQL Diff & SQL Log backups show 0 bytes under Restore Job, Selections page

[Steven_Smith]

I am trying to do a SQL restore from my SQL Full, SQL Diff and SQL Log backups.

I can restore SQL Full with no problems but when I try to add SQL Diff on top of the Full, I get this error:

"Could not find database ID" error.

I did find one thing that might be my problem. In BE, on the 'Restore Job' , 'Selections' page, my SQL Diff and SQL Log backups show up there as 0 bytes! I know the files are there and the backup jobs have good byte count when they complete so I can't believe they are 0 bytes. The Diff and Log backup jobs both complete with no errors and the byte size is certainly not 0 when the backup job completes.

Has anyone seen this before?

Any ideas?

[Steven_Smith]

E-mail Support and Support Forums
Please note that effective January 13, 2005 VERITAS Technical Services will migrate select Technical Support service requests from E-mail suppot to our new moderated Support Forums.

We believe that this will provide a much richer experience, allowing access to the knowledge and experience of other VERITAS customers as well as to the VERITAS Support Staff.

VERITAS Backup Exec for Windows Servers version 9.1 no longer offers e-mail support. For all electronic support for this product visit our moderated Support Forums.


REALLY?!?!? Much Richer??! How? By ignoring people? That's richer???

[Steven_Smith]

Sorry for the rant, actually did not mean to send that when i did :-/. I am gettia littel frustrated with this error. I can not restore my DB until I resolve this.

I can't find any info on how to properly backup and restore SQL with BE9.1 correctly and also nothing specific aboutthe error message.

Any idea on the error message?

[Keith_Langmead]

Afraid nothing comes to mind which would explain your problem, but I don't currently have a v9 installation to refer to check it out (v8.5, 8.6 and 10.1 yes, 9 no!).

Can you post EXACTLY what you are doing, step by step through the restore process, both for the originial db restore and then the diff restore, with all the options, what you enter where etc. With any luck it might spark mine or someone elses imagination into a solution, though I obviously can't promise anything.

Keith

[Steven_Smith]

Hi Keith,
Thanks. Here's what I have. For backups, we do SQL Full everyday at 9PM, SQL Diffs every hour and SQL Logs every 15 minutes. We are approaching 1M transactions per month btw. I inherited this environment and I need to know the proper way to restore a SQL 2000 DB.

For the Restore process, I first restored the latest SQL Full using SQL Redirection. I also made sure to use the 'Leave DB non-operational.." option so that I can add either the Diffs or the Logs on top of that. The SQL Full restore runs fine. I am able to restore that one with no problems, even with re-direction.

My problem is I am not sure what I am suppose to restore next. I thought SQL Diff comes next. However, when I try that, on the 'Restore Job' --> 'Selections' when I navigate to any of my Diffs or Log backups, they all have a byte count of '0' on this page. My Fulls do not have '0' byte count. I don't understand why it says '0' bytes. In 'Job History' my Diff and Log backups have good byte counts when they complete and no errors either. Is it normal to have a '0' byte count on this page for Diffs and Log backups?

I think this is my problem because when I attempt the Diff restore, I get the error stating "An error occurred on a query to database XYZ. Could not find database ID 65535. Database may not be activated yet or may be in transition."

Here are the settings I am using for the backup jobs below. Note that my SQL Full backup job does include the system DBs (msdb, master model and pubs). Should those be removed from this Job? Will having those in there mess me up some how?

SQL Full Overwrite, Full - Reset Arc Bit, Verify, No SI, With JPs, AO Never, Entire DB.

SQL Diff Append w/Overwrite, Diff - Changed Files, No Verify, No SI, With JPs, AO Never, Diff - Changes only.

SQL Logs Append w/Overwrite, Full - Reset Arc Bit, No Verify, SI Yes, With JPs, AO - With a lock, Log - Backup Transaction Log.

SI - Single Instance
JP - Junction points
AO - Advanced Open

And that is basically it. All the other settings are defaults. Any help you can provide would be greatly appreciated.

Thanks again!

[Keith_Langmead]

Well that looks fine to me, I'm afraid I can't see anything wrong with what you're doing.

I agree that the question of the 0 byte count seems suspicious, there's been at least one other post (in addition to your other thread) recently with a similar issue in it, though unfortunately after having a look through the posts I couldn't spot it, so the subject line might not have been obvious. From memory in that thread the poster also said she was seeing something similar.

If you were able to determine why the byte count is showing up like that I wouldn't be suprised if you found that resolved the issue, but I'm not sure what you could try. Because of the byte issue I'd suggest that you need to concentrate you efforts on the backup job rather than the restore one, and see if altering the job will make the difference. Perhaps experiment with some of the SI, JP, AO etc aspects.

Keith

[Steven_Smith]

Keith,
Ok I can try adjusting the Backup jobs and see where that gets me. Can you answer one last question for me as I've never gotten an answer on this one.

If I am backing up SQL Full everyday at 9pm, SQL Diff every hour and SQL Log every 15 minutes what is the general process for a SQL Restore? I know I should restore the Full first but then what? I assume the latest Diff goes next but where do the Logs fit in all this? When do the Logs get restored? After the Diff? Do I restore all Logs since the Diff? I am confused on this part.

Thanks again.

[Keith_Langmead]

I think you're right, that you'd need to through restoring the Full backup, then the Diff and then finally the Log. That said I've never actually done it myself, since none of our SQL databases required that level of backup, so I've just done Full and Incrementals.

Even so, if we degraded your solution to my definite known knowledge, you should definitely be able to restore the Diff after the Full, even if you couldn't do the logs after that.

I hate to say it, since I know the quality of the docs, but your best bet might be to consult the admin guide and see what that has to say. If nothing else it might give you a greater grounding on how Backup Exec handles all this.

[shweta_rege]

Hello,




-- What is the SQL Version that you are using?


-- Are you trying a Redirect Restore?

-- In order to resolve the SQL database redirected restore issue, please make sure that you are restoring the database as well as all the respective transaction logs. The database and transaction logs should be restored in proper sequence for point in time recovery of the database. You may like to perform a test restore by selecting the 'Recover database' option and then from the SQL Query Analyzer run the following SQL Statement:

--> LOAD DATABASE WITH RECOVERY




Thank You.

Shweta

[Steven_Smith]

Shweta,

Thanks. For Backups, I do SQL Full every night, SQL Diff every hour and SQL Logs every 15 minutes. I am trying to restore a SQL 2000 DB with BE 9.1.

Yes, I am doing a re-directed restore. I first restored my SQL Full and that works good, even with re-direction. I make sure to use the 'Leave DB non-operational..' setting so that Diffs and Logs can be added.

After I restore the Full, what is the next step? If I am doing all the backups mentioned above, WHAT IS THE PROPER SEQUENCE for a point in time restore? No one has answered this question for me precisely. I assume the sequence is this:

1) Restore the latest SQL Full (Leave DB non-op)
2) Restore the latest SQL Diff (Leave DB non-op)
3) Restore all the logs in sequence since the Diff in step 2 above. All restores, except that last one, leave the DB in the no-op state.

Is that correct? I really need to know if I am wrong on these steps.

If the above is correct, I am still having an issue with step 2. When navigating the 'Restore' - 'Selections' page, all of my Diff and Log backups show up on this page with 0 byes! I know the Backup Jobs run fine with no errors and those same Backup Jobs have good byte counts when they complete so I do not know why they then show up with 0 bytes on the 'Restore' - 'Selections' page!!?!?!?

Even though the Diffs show up with 0 bytes, I still tried restoring the Diff. That is when I get the error below. The Diff restore fails so there's no way that SQL Query Analyzer will help there.

"An error occurred on a query to database XYZ. Could not find database ID 65535. Database may not be activated yet or may be in transition."


I need to know 2 things.

1) What is the proper restore sequence for a point in time restore of a SQL 2000 DB when I have SQL Full, SQL Diff and SQL Logs backed up as mentioned above?

2) Why do all my Diff and Logs show up with 0 bytes on the 'Restore' - 'Selections' page when the Backup Jobs don't fail and those same Jobs complete with good byte counts?

Thanks!
Steve

[Steven_Smith]

Can anyone answer my two questions above?

Thanks in advance :)

[Steven_Smith]

Anyone?

Bueller? Bueller?

[Gauri_Ketkar]

Hi Julia,

1) What is the proper restore sequence for a point in time restore of a SQL 2000 DB when I have SQL Full, SQL Diff and SQL Logs backed up as mentioned above?


Please refer the admin guide for the exact steps for restoring SQL

Restore Options for SQL 2000
page > 1018

http://seer.support.veritas.com/docs/266190.htm

Restoring from SQL 2000 Transaction Logs Up to a Point in
Time
page > 1025


2) Why do all my Diff and Logs show up with 0 bytes on the 'Restore' - 'Selections' page when the Backup Jobs don't fail and those same Jobs complete with good byte counts
* Go to restore selection to check whether what is being shown there in Diff and Logs backup set ... 0 bytes ?



Update us on the same and revert for any further Query



Thank you
Gauri

[Steven_Smith]

Gauri,

Well thanks for the info but I've read the Admin guide before I ever posted here. I've read the pages and sections that you mention but all the guide does is explain the settings in a restore. That's helpful but not what I am looking for. I was hoping for a Process that explains the entire Restore process starting with the Full restore, then Diff and then finally Logs to include the recommended options for each restore. All page 1025 explains is how to restore one Log using the 'Point in Time' option.

Regardless, I have figured out the correct process via trial and error and documented it myself. There are a lot of settings to keep in mind with all these restores and that was the detail I was looking for. The Admin guide lacks these details.

However, my second question had not been answered anywhere. You mention that I should go to the Restore selection to check on the byte size but that's exactly what I am doing. On the Restore page, all my Diffs and Logs show up as '0' bytes. However, this must be normal since my restores are working. I am able to restore the Full, Diff and Logs. Even though the Diff and Logs show '0' bytes on the Restore age, they both restore with good byte counts.


Why do all my Diff and Logs show up with '0' bytes on the 'Restore' - 'Selections' page when the Backup Jobs don't fail and those same Jobs complete with good byte counts? This appears to be normal, can any one confirm that?

Thanks again!

[Deepali_Badave]

Hello,

Please refer the following technote:

A restore of SQL databases completes successfully even though the job log reports 0(zero) bytes restored

http://support.veritas.com/docs/248262

Regards,

[Steven_Smith]

Deepali,
Thank you but there are two problems with this link.

1) I am using BE 9.1 and the link you gave me is not for this version

2) This is NOT the symptom I am seeing. My Restore jobs complete fine with good byte counts.

When you click on 'Restore', the Restore job page launches and the first page is the 'Selections' page. On this page, all my Diff and Log backups show '0' bytes. This is the ONLY symptom I am seeing. Even though on this 'Selections' page the byte count is '0', my restores complete with good byte counts. I simply want to know if this is normal?

Again, on the 'Restore' - 'Selections' page (not the Job log), all my Diff and Log backups show '0' bytes even though those same Backup jobs completed with good byte counts.

[Steven_Smith]

Deepali,
Thank you but there are two problems with this link.

1) I am using BE 9.1 and the link you gave me is not for this version

2) This is NOT the symptom I am seeing. My Restore jobs complete fine with good byte counts.

When you click on 'Restore', the Restore job page launches and the first page is the 'Selections' page. On this page, all my Diff and Log backups show '0' bytes. This is the ONLY symptom I am seeing. Even though on this 'Selections' page the byte count is '0', my restores complete with good byte counts. I simply want to know if this is normal?

Again, on the 'Restore' - 'Selections' page (not the Job log), all my Diff and Log backups show '0' bytes even though those same Backup jobs completed with good byte counts