usefulness of LDF files if never backed up?

Question

usefulness of LDF files if never backed up?

IrieMon69

SSC Enthusiast

Points: 178
More actions
June 28, 2007 at 2:33 pm

#174210

Situation:

SQL 2000 SP4 and also SQL 2005 SP2
Some db's on each version with data i/o deemed important enough to be in Full Recovery mode to allow point in time recovery.

Veritas Netbackup used to make full backups nightly, TLog backups are NEVER run via Veritas or EM - we have no control over the Veritas process and are told that the EBR team cannot do it due to some logistical/technical issue. For the purposes of this example we will assume they are correct and our solution must include SQL only. Drive space is not avail and we cannot implement log file backups to local drives.

Two problems I am trying to resolve/determine answers to:
1) TLog never being backed up allows log file to grow unchecked - I know there are other solutions to this issue, but would prefer that a valid backup/restore scenario be part of the file size solution
2) As noted above, EBR team never backs up TLogs, so I want to determine if there is a valid reason for keeping the db's at Full recovery (if we can't use the TLogs for restores, we could recoup much drive space by setting to simple)

From perusing these forums, it seems that the ldf file itself is not useful to us from a restore perspective, other than possibly via 3rd party app (I've heard Lumigent LogExplorer or Red-Gate SQL Log Rescue mentioned, haven't had time yet to download/review them) or with the scenario mentioned below.
QUESTION: Is there any way to perform a restore either to a PIT or with all TLog transactions included with just the Veritas Full backup restore + ldf file?

An option I believe I've put together from these forums is this, please tell me if this would work assuming all backups are valid and ldf file is still avail:
a) Database is backed up in full nightly, at some point during the day data/schema is deleted or corruption occurs
b) Veritas full db restore is performed on secondary server
c) local EM TLog backup is performed on Prod db
d) PIT restore is done on secondary server on top of full db restore
e) data in Prod db is either replaced with valid data from restored db, or entire db is overwritten as appropriate

Any thoughts, comments, info is appreciated!

David

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Wayne West SSC-Insane Points: 22586 More actions · Answer 1

I think your plan looks reasonable, the only way to know is to try it. Are you using a backup agent? A lot of people prefer backing up the database to a local file via a SQL Server job, then using a 3rd party tool to back up said local file, and that's definitely my preference. I've had some uncomfortable experiences with backup agents that go directly into the database.

-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]

IrieMon69 SSC Enthusiast Points: 178 More actions · Answer 2

Thanks for the reply.

Our EBR team does use a Veritas SQL agent to back up the db's directly; Unfortunately, we do not have drive space available to perform local EM backups and then have the EBR team perform file-based backups - some of our db's go up to 1.5 terabytes in size.

As for trying my plan - I can perform a test restore of the process using a TLog backup, but I cannot find any documentation regarding restoring directly from the LDF file. I'd be interested to hear from someone more knowledgable than me whether it's possible.

Thanks again -

Wayne West SSC-Insane Points: 22586 More actions · Answer 3

Yeah, I meant to ask how big of a DB you were dealing with. My biggest DB is only 140gig, and it's not a production DB. My biggest production is probably only 3-5gig or so.

I was approached by a recruiter (several times!) about a job in Dallas wrangling a similar sized DB. I told him no, I have no experience with that large of a database, and doubly-no because it was a 24/7 extremely mission critical system. I wouldn't have minded a junior DBA role where I could be mentored and learn the differences, but they wanted a senior.

Unless there's some sort of third-party tool, I've never heard of restoring directly from an LDF. The normal procedure is to restore the transaction log to the DB after the DB has been restored to its latest point and everything gets applied.

Good luck with it, David!

-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]

Greg Charles SSC-Forever Points: 45403 More actions · Answer 4

David,

You can't restore directly from a transaction log file, only from a backup of a transaction log file. Your original plan listed making a Tlog backup to restore on the secondary server after restoring the database backup. That is what you should shoot for.

Greg

IrieMon69 SSC Enthusiast Points: 178 More actions · Answer 5

OK, so from the sound of it there is some benefit to us keeping the important db's set to Full Recovery even if TLog backups are not run on a consistent basis - as long as we don't lose the TLog drive or have a corrupted TLog along with a db or data needing to be restored I can run a TLog backup when needed and then restore to a PIT.

Thanks for the reply, it will be good to be able to go back to my team with some definitive answers as to how we can best protect the data with what we have to work with.

Wayne West SSC-Insane Points: 22586 More actions · Answer 6

David, you might want to make sure to copy your transaction log to alternate locations, just in case. I copy my tlog backups to a SNAP server after they're backed up. I would also make sure to do a verify on the tlog backups: it doesn't guarantee the data, but at least you know the structure is intact.

-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]

Wayne West SSC-Insane Points: 22586 More actions · Answer 7

Stupid reply-eating so-and-so system....! That's twice this week that it's eaten a reply!

David, I totally missed the point that part of your original question was the benefit of full vs simple recovery. Keep it in full! If it breaks, any transaction log pieces will be better than none.

Do you have a SNAP server or similar device with a decent amount of space on it that the log could be backed up to? You can't create database or log devices on a SNAP server, it's just raw storage. But you can create backup devices there.

My personal backup methodology is to do a full tlog backup at night, then a full data backup locally. I then repeat the data backup to the SNAP server. I do transaction log backups locally during the day, the second step of that backup copies the log backup to SNAP. And at noon I do an incremental append locally and then to SNAP. To top things off, twice an hour the SNAP server is backed up off-site by my Tivoli system.

So I've got in-box, locally out of box, and off-site.

But there's no reason why you can't back up your transaction log outside of the box as long as you have enough space somewhere else.

-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]

Strommy SSCrazy Points: 2386 More actions · Answer 8

Hey David,

From my experience with Veritas and with large databases, I have a couple comments to make, as well. First off, I would recommend checking out LightSpeed, a DB backup compression utility. It will do backups and restores at about 1/5th the size. At least, it may be workth checking out.

As for the SQL Agent for Veritas, it should be able to do full backups, diff backups and log backups. You may need to work with the team to get that set up, but I would recommend taking advantage of all 3 in your backup model. You could save backup time and space using diff backups (assuming your database change is small in relation to the total size). Also, having t-logs off server will greatly improve disaster recovery (as opposed to a database oops, like table dropping). At any rate, I say be a little more of a pain in the butt to your back-up team. I did and our backup reliability went way up.

Thanks,

Eric

IrieMon69 SSC Enthusiast Points: 178 More actions · Answer 9

Thanks Eric, that info is useful and appreciated. My choice would definitely be to have Veritas handle everything including full db backups, differentials, TLog backups and restores where appropriate. I'm hoping that as we push internally to make it known how incomplete our backup strategy is that upper management will begin to question why our EBR team is not able to perform the TLog backups. They are constantly fighting for disk space to do full backups of our 1TB+ db's nightly, when they could easily be using a diff format that would save the company mucho dinero and put much less load on the servers.

When we finally come to some determination I'll post an update here, just as an FYI.

David