SQL Server Performance

backup strategy

Discussion in 'Getting Started' started by anchor, Sep 18, 2007.

  1. anchor New Member

    Hi Gurus,
    Can anyone tell me what would be the best backup strategy for a company.
    Thanks in advance.
  2. thomas New Member

    Full backups* to disk every night. Regular transaction log backups to disk (on a separate server) regularly, at least every 15 minutes, possibly sooner.

    All backups held on disk should be copied to tape every night. Tapes should be retained for a month, with one set per month archived for longer (a year normally).

    Also non-sql server data (e.g. files, registry, installed programmes, scripts, etc) should be backed up to tape regularly.

    All system databases should be backed up nightly too.

    All source control (e.g. source safe) databases, documentation etc. should be backed up too.

    *Some people like differential backups but I dont.
  3. vipulkumar25 New Member

    Hi Thomas,
    I am agree on your point that we should take transactional backup after every 15min. , but if we increase the frquency then the chekpoint process and transactional backup jobe may get clashed and will create huge bloacking and system will not able to respond. So what do you say in that case?
  4. thomas New Member

    Checkpoint shouldn't block t-log backups. In 8 years of working with sql server I've never seen that happen.
  5. vipulkumar25 New Member

    Hi thomas,
    In my case my db size was 400 GB and 700 transactions pers ec.(tps) were hitting the database. what is your system configuration ?
  6. thomas New Member

    My busiest one currently is 60GB and about 70tps. Anyhow, back to the matter in hand, log backups every 15 minutes should suffice.
  7. vipulkumar25 New Member

    thomas I am not disagreeing that 15 min t backup will not suffice, but there are chances that these two process will clash , in my senario i am also taking backup efter every 15 min and doing log shpping but the problem which i mentioed occured 2-3 times thats why i discussed this issue.
  8. anchor New Member

    Thanks allot guys for such a warm response. Another simple question, is is necessary to include differential backup in your full database backup strategy or we can omit. I mean to ask Will it be ok if we take only full backup and transactionl or we have to used differential also.
    Thanks in advance again.
  9. thomas New Member

    There is no need for differential backups, I've never really seen a need for them.

    As far as I can tell the main advatange of them is that you have fewer t-log backups to restore in the event of a disaster. But you will always need full backups regularly anyway, so you might as well rely on full backups and log backups. Diff backups just add another layer of complexity you can do without.

Share This Page