SQL Server log backups show 0 KB in restore panel ?!?

[robert_rogers]

If I'm going to restore my SQL Server, what does it mean when a backup of SQL Server log files shows 0KB in the restore panel?

In the job setup properties box, I have the following selected:
- General Backup method for files = "full - reset archive bit"
- SQL Backup method = "log - backup transaction log"
- SQL Consistency check before = "none"
- SQL Consistency check after = "physical only"

NOTE: in the Job Monitor log, this job always shows successful completion and many KB's of data having been backed up. No errors are being generated or reported anywhere.

[Ajit_Kulkarni]

Hello,


You have mentioned in you post that you are going to restore the SQL Server. I would like to know if you are trying to do a normal restore of SQL databases or the disaster recovery of the whole system ?
Was the backup successful ?

Kindly update.

Regards


NOTE : If we do not receive your reply within two business days, this post would be marked "assumed answered" and would be moved to "answered questions" pool.

[Ricardo_Fernand]

Exactly the same Problem here.
Its normal?

[robert_rogers]

I have not yet tried to do a restore of the database to a point in time using the backups of the logs.

I simply want to know *WHY* the restore panel shows different information from the job monitor log.

Can you tell me why these two area of Backup Exec show different information? Does this indicate a problem?

[Ricardo_Fernand]

Can someone explain this situation please?

Best Regards,

Ricardo Fernandes

[Joseph_Garcia]

Where precisely does it show 0 KB on the Restore tab? I have looked and can't find where you're talking about.

Joe

[shweta_rege]

Hello,

What do you actually mean when you say:

" *WHY* the restore panel shows different information from the job monitor log."

- Could you please elaborate

******************************************************************
*****************************************************************

Note : If we do not receive your reply within two business days, this post would be marked ‘assumed answered’ and would be moved to ‘answered questions’ pool.


Thanks.

[robert_rogers]

When I look in the job monitor, I see records about my log backup jobs, which say, for instance:
STATUS: successful
PERCENT COMPLETE: 100%
ELAPSED TIME: 0:19:12
BYTE COUNT: 135,264,879

... and this looks good to me... about what I'd expect.

On the other hand, if I go to do a restore using data from one of these log backup jobs, I see the following types of info in the resotre window:

(by highlighting one of my FULL backup jobs):
I see: SIZE: 48,123,534KB
... this also looks good to me - about what I'd expect.

(by highlighting one of my LOG backup jobs):
I see: SIZE: 0KB

... and there's the problem:
I know that the log backup jobs have scanned (and hopefully, backed up!) a few MB of data (as seen in the job monitor). **** SO WHY DOES THE BACKUP JOB SHOW 0KB IN THE RESTORE WINDOW ?!?

[Joshua_Small]

As a suspicion, I would suggest this is to do with the way SQL transaction logs work, particularly if your SQL server is in the "full" recovery mode.

Consider this situation, you have a 200MB data base, and a 5MB log file. Backup works like this:

Step 1: Commit log file and wipe it. Log file size = 0
Step 2: Backup database

This is why logs always shrink after a backup.

If however you were doing a incremental or differential backup, you would instead see the "database" backup = 0MB, where all your backup is the log file, due to the way logs work.

Of course, if your database is in the "simple" recovery mode I do not know how this may apply.


Note to Veritas: I would appreciate if you didn't jump on my post and try to make it look like you resolved the issue, as you do whenever I post.

[robert_rogers]

So are you saying (at least, in theory) that the "0MB" which I see in the restore panel is the size of the log file *AFTER* it was backed up (i.e. back up logs first, then truncate log) ?

That seems a very odd way to display information in a "restore data" type of environment... but then: you didn't design it ! It's a thought, anyway...

[Joshua_Small]

Hi Robert,

Yep, that's what I'm saying. To clarify:

- All log data is commited (written to database) BEFORE backing up, and is hence redundant. But the data is still there, which is why it shows up on your "backed up data bytes" information
- After backup, the log file is truncated
- During restore, the system restores the database only, as that's all it needs.

There's a lot of guessing on my behalf there, and yep, I would have designed it differently.

That said, I see exactly the activity you see on all my servers, and I've tested and shown it's always restored successfully.

That said, I've had more _manageable_ success by dumping Veritas' SQL agent.
By that, I mean do this two step process:
- Configure enterprise manager to take a backup to disk every day at 7pm
- Configure BE to backup that file, along with the rest of hte server, at 8pm

Depending on file sizes this may not work for you, but I've found you need to do this if you want to avoid Veritas breaking or becoming unsupported every time Microsoft release a new service pack for SQL server, and there's far less to go wrong, since restoring a file is much easier in BE, and there's always Microsoft techs that actually provide product support for restoring those files.

[Asma_Tamboli]

Hi Robert,

Please let us know if you have any further queries.



NOTE : If we do not receive your reply within two business days, this post would be marked assumed answered and would be moved to answered questions pool.