March 21, 2008 at 3:52 am
Hi,
If we are planning to have a DB Maintainance Backup plan, please let me know should i use Append option or overwrite option and also please let me know which one is better and the reason. Please help in this
March 21, 2008 at 8:05 am
It depends on what you need.
Your full backups and log backups should generally be apended. And the prior day's backups should be on tape (or some other separate medium), before you initialize/overwrite them.
Differential backups can be overwriten, since there's not really a reason to use "the prior differential backup".
I like daily full backups, differential backups either every 4 or every 2 hours, and log backups every 15 minutes, on production OLTP databases. The timing depends on how long it takes to run the backups, and how much data the business can stand to lose.
On an OLAP database that gets loaded every night (for example), I'd be much less aggressive in my backups. Do a full backup with overwrite (initialize) after a successful load operation, then copy the backup file to tape, and that would be all.
(You can use a remote drive instead of tape. Or something else.)
And, just because it almost can't be said too often, test your backups regularly. Make sure you can restore from them, and find out how long it takes to do so.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 21, 2008 at 8:50 am
I never append. I've had cases where the file gets corrupted and then you lose a bunch of backups.
I always overwrite, but I use a new file name for each file, which has the date in the file name. This allows me to find the files and determine which ones I need.
I also make sure the new backup is written before the old is removed.
March 21, 2008 at 9:03 am
I haven't run into the file corruption issue, but I can see that separate files would make sense.
Since the point is to avoid accidentally getting rid of a backup when you might still need it, that certainly works.
There was a thread recently where someone proposed using "With Init" on all of the backups, without realizing that any sort of failure during the backup operation would mean a complete loss of the database. Anything that avoids that problem is what really counts.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 22, 2008 at 9:08 pm
WITH INIT should have no effect on the database. It does remove any backups in that file, but that's separate from your database.
I use WITH INIT for clarity, but always create a new file.
March 23, 2008 at 10:48 pm
Thanks a lot for all your reply.
March 23, 2008 at 10:48 pm
Thanks a lot for all your reply.
March 23, 2008 at 10:49 pm
Thanks a lot for all your reply.
March 23, 2008 at 10:49 pm
Thanks a lot for all your reply.
March 25, 2008 at 7:07 am
Steve Jones - Editor (3/22/2008)
WITH INIT should have no effect on the database. It does remove any backups in that file, but that's separate from your database.I use WITH INIT for clarity, but always create a new file.
That works, with your new file for each backup scheme, but that's not what was being proposed in the plan I was refering to. This was a backup plan that used the same file, with Init, for each backup full backup, and so on with differential and log backups.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 21, 2011 at 4:02 pm
My Scenario is pretty much same as you replied.
We are appending now last TB Backup because my manager want to keep old backup file untill new backup completed.
but how automated restore process knows which is new backup file and which is old one?
can you explain me in detail what you said in post reply please?
is that okay to overwirte for large databse?
December 1, 2011 at 7:41 am
Hi ,
Replying on your Post Sentance
"I always overwrite, but I use a new file name for each file, which has the date in the file name. This allows me to find the files and determine which ones I need.
"
How can know Diferent dat in file when you over write it ?
I am thinking to make DIfferential Backup to Overwrite is that good choice?
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply