September 26, 2011 at 2:51 pm
I've read some confusing information on log shipping and full backups so now I'm not sure on the recovery process with transaction logs when I'm also doing a full nightly backup.
The full backup is a native backup SQL Agent job that happens nightly at 1:05am. The log shipping was configured to run every 15 minutes between 1:10am to 1am. So everything is running great and all but should disaster occur and I need to recover the database, would I restore the last full backup from 1:05am, then start restoring each of the following tranlogs, (the first one being from 1:10am), and so on? (And a secondary question, if this is correct, I wouldn't have to save any transaction logs older than the last backup, right?)
Basically I just wondered if the Full backup has to happen in some specific manner in order for the LSN of the next log to match to that backup or if it does so automatically.
Any insight would be appreciated.
Thanks
September 26, 2011 at 2:54 pm
no you are correct, although keeping the full backup and logs from the days before wouldn't hurt. Dont want to delete them then find out your last full backup is corrupted.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 26, 2011 at 3:05 pm
Hey thanks for the confirmation. I was second guessing myself there.
Thanks for the advice too. I actually restore last night's backup to our backup server each morning as a database for reporting (so users can get all access up to yesterday's data without hitting the production database) so I'm basically "testing" the validity of the nightly backups every day.
I'm feeling just a little more confident then...
😉
September 26, 2011 at 3:57 pm
I'm a little confused by this as well.. Why are you even managing the restores yourself.. Log shipping should be keeping the other end current with the most recent log dumps, you shouldn't need to really do anything. All you might need to restore is the most recent log dump or 2, and then recover the database, in most cases you probably won't have to restore any dumps or at most one..
I would keep the full backups 2-3 days and the log dumps 2 or so days regardless of whether I'm log shipping or not.
Did I completely misread the question?
CEWII
September 26, 2011 at 4:12 pm
I think you might be. The nightly restore I mentioned is on a different server altogether, for a different purpose, outside of the log shipping process and done only so that reports can be run off of that database rather than the production database. The log-shipped secondary database is completely separate from this process. (I'd run reports off of the log-shipped database but it's not in a state where that's possible (Restoring) so thus, the "extra" reporting copy of the database.)
And I do keep a number of full backups saved (goes back months on tape). Now, correct me if I'm wrong, but I don't see how keeping logs older than 1:10am day before would be of any help since the logs only apply to the last backup done. Since I confirm my last backup is not corrupt (by restoring it successfully to my "extra" reporting copy of the database every evening), I wouldn't need the logs prior to that restore.
Does that make more sense?
September 26, 2011 at 4:20 pm
you may want to go back to a point before your last full backup, so you would then need logs from before that backup.
you can restore logs and the leave the database in a read only, standby state, thus allowing you to run reports against it.
---------------------------------------------------------------------
September 26, 2011 at 4:31 pm
I guess I'm just looking at this process in my own environment. I can't seem to think of any scenario where i'd have to go back to a point BEFORE my last full backup, but AFTER the full backup before that one. I don't think I'd ever have that situation at our site. If something happens to the database, it'll happen now, or at least at some point today, for which I'd be dealing with the tranlogs and previous backup. (Oh well, since I haven't been doing this long, I might just be a little narrow minded on this)
And for the read-only/Standby state, that's true. However, as I understood it, when the tranlogs are getting applied to the database and a report happens to run at the same time (I have a number of automated reports that go out as well as users to manually access them in SSRS), the report cannot be generated/fails. Then I get all kinds of angry phone calls like "Where's my report??" or "The reports are broken!". I guess I'd rather have a smoother more transparent environment to the users and just maintain the extra database (well, it pretty much maintains itself with jobs).
September 26, 2011 at 4:47 pm
You need to understand log shipping a little better.
At the moment you are restoring the last full backup and all logs for the previous day in one go. You can have log shipping do this.
The backup job would run every 15 mins (or whatever you specify)
The copy job would also run either every 15 mins or just run it once to copy all files.
The restore job would run once (at the same time you restore currently)
Nett result is database is read only, and upto date based on previous day and no user disconnections
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 26, 2011 at 4:50 pm
Just because you verified the file doesn't mean something can't happen to the file on disk 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply