Restore Transaction Log
-
My apologies if this topic is too remedial. But trying to get my ducks in a row here. Have been tasked with creating new backup/restore and disaster recovery plans.
We run several daily jobs that move data (text file source) from a retail system mainframe via dts to SQL 7 (Select Into / Bulk Copy = True). Of course we create non-logged transactions. And of course our Transaction Log backup fails.
What I've read is to execute full backups on a certain schedule, incrementals, and transaction log backups. When a restore is needed, the most recent full backup is restored, then most recent incremental restored, then however many transaction log backups exist from the incremental to present time.
Is this the best solution, and will that get me back to the most recent possible point in time?
-
Yes, this solution will allow full point in time recovery.
Depending on the database size and the amount of data updated between the full and differential backup, you may find that taking only full backups may be more efficient. What is the ratio of the size of the differential and the full backups? The closer the ratio is to 1, then the closer the differential backup is the equivalent of a full backup.
Depending on your mainframe loading architecture, an alternative is to have 2 databases: One with the tables that will stage the mainframe data and the other database for rest of the tables. Different backup solutions could then be applied for each database.
SQL = Scarcely Qualifies as a Language
-
Good suggestion. Thanks.
-
Just to be clear, you have FULL, DIFFERENTIAL and INCREMENTAL backups.
Full backups are what they sould like.
Differential are what you referred to as incremental backups.
Then you have incremental backups which are commonly referred to as transaction log backups. Just make sure you get the terminology right when getting the commands from books online so you don't run into grief if a restore is ever required
As Carl said, you might find that just doing full backups each time may be just as good if the entire DB is changing, or, for simpler restoration, you could just do a full backup and then take differentials each day. I usually prefer to do this on sites where I don't fully trust the backup tapes - for a full backup taken saturday and differentials done each night, I just need saturday's and thurday's backup if the DB crashes on friday. If I used incremental backups, I would need Saturday's, Monday's, Tuesday's, Wednesday's and Thursday's backup media to be in full working order!
-
I caught my terminology mixup this morning when I checked my post. Sorry, it was a rough day. As you said, I meant Full, Diff and transaction log. My understanding is that the Differential is a full backup "light" (contains only changes from last full backup).
We currently run one Diff backup (1x per day) which takes about 30 minutes, but would like to be able to apply a transaction log backup. But as I stated, due to the data transfers (Select Into), the transaction log backup fails. Guess just no way to backup that transaction log.
I suppose that asside from turning that option off and loading legacy data into a second or "staging" server (which is a good idea), that we would just schedule more frequent differentials. Sound about right?
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply