September 19, 2005 at 1:15 pm
I just started working at a new company and the current backup procedure is this:
Full backup Weekly
Differential, init Nightly
Transaction Logs Hourly with noinit (this file gets huge since its the whole weeks transactions)
Transaction Log Weekly with init
to clarify, the log is appended during the week and then copied to another server before it is overwritten by the weekly with init job. The file gets huge.
I have several questions..
1. what happens to the differential backup when init is used? does it wipe the previous day's file and only save the current day, or does it save all changes since the last full backup?
2. Supposing our system crashed at noon on a wednesday, my inclination would be to restore the full backup and then the transaction log up to before the crash time, and skip the differential since the transaction log has every transaction since the last full backup. I would think that the way they have this set up, the differential backup is useless. ideas?
3. Does anyone know what would happen if i restored the full backup, the differential from the previous night, and then the trans log with the full week's transactions.
Thanks
Regards, Jim C
September 19, 2005 at 1:48 pm
1) Keeps current day
2) Differentials will speed up the recovery eventhough you can use just the logs
3) If you are doing this from EM it will disregard the log activity previous to the differential and use the ones after it. (It is ONE File but it contains MULTIPLE TLog Backups )
Honestly I have seen new TLogs with init most of the time. That way files are more manageable (in size) and for recovery purposes you just need Full, Differential and the TLogs after diff
Cheers!
* Noel
September 19, 2005 at 2:59 pm
Thanks for the reply..
could you clarify one thing for me? my first question about the differential, init...if we run a full backup Saturday night, then differential with init each night and say Thursday, daytime the server goes down, would the differential backup created Wednesday night cover that whole time from Saturday to Wednesday night?
Regards, Jim C
September 19, 2005 at 3:11 pm
Thats right.
Restore would be:
*restore full backup of saturday with norecovery
*restore differential backup of wednesday with norecovery
(this instead of applying all transactionlogs since saterday, which would take a long time)
*apply any remaining transaction logs of thursday with norecovery (with point in time if necessary)
*make the database operational :restore database with recovery
September 20, 2005 at 6:09 am
Every differential backup is of the changes since the last full backup.
Mon - Full Backup - 6 AM
Diff with init - 6 PM
Tlogs every hour
Tue - Full Backup - 6 AM
Diff with init - 6 PM
Tlogs every hour
Wed - Full Backup - 6 AM
Diff with init- 6 PM
Tlogs every hour
Tables accidentally deleted on Wed at 730 PM.
Do a transaction log backup if possible.
Restore the Wed Full Backup, Wed Diff, the 7PM Tlog and if available the very last Tlog using WITH STOPAT to stop the log restore prior to the delete time.
-SQLBill
September 20, 2005 at 6:12 am
Even if you only do the Full Backup once a week (lets say Monday), it would be the same as my previous example:
Restore Monday's Full Backup, Wed's Diff, the Wed 7 PM Tlog and the final Tlog if possible using WITH STOPAT.
If only one Full backup is done a week (lets say Monday) then, Monday's Diff has all the changes since Monday's Full backup, Tuesday's Diff has all the changes since Monday's Full backup, etc.....
-SQLBill
September 20, 2005 at 9:30 am
Perfect. that's exactly what I was looking for. Thank you.
Regards, Jim C
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply