Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2004
    Location
    The Netherlands
    Posts
    29

    Question Unanswered: Howto work and manage backup log files???

    Hello everybody,

    I have run into some problems with logfiles and backup logfiles (MS SQL server). I have read much about them but uptil now I dont seem to grasp how it works. Specially the part of working with backup log files.

    Can someone please help me with this?

    What I want todo is to create backup log files, so I can restore the database to any point in time before a failure occured. But using this method create's a problem with the backup log files. They get very big.

    So how can I use backup log files without getting to large backup log files?

    How can I shrink on a regular basis the backup log files and still be able to restore the database?

    Please help.

    Best regards,

    Marc Janssen,

  2. #2
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    FIrst of all confirm the following :
    How big is the Transaction log file?
    What is the schedule of backup interval of Tlog?
    Any network issues between live and backup servers?
    What kind of transactions performed on the database?


    Its not worth shrinking the Tlog on a schedule basis when frequent updates occuring to the database.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    A basic backup schedule consists of a scheduled database backup with intermittent log backups.

    For example, you might run a database backup once a week, and then run log backups every evening. On a more critical database you might run a backup every evening and a log dump every hour.

    The log dumps will only contain the transactions since the last log dump or backup, so they should not grow too big.

    You should also establish a backup cycle for retaining your database and log backups. For instance, if you backup your database every evening with hourly log dumps, you might just want to retain your log dumps for 2 days, allowing you to do point-in-time recover for corruption issues less than 2 days old. The nightly backups you might decide to keep for a full week, just in case you want to recover data. You might also decide to keep weekly backups (Sunday evening, for instance) for a full month, and retain the first backup of the month for a full year. For good measure, you could burn an annual backup of your database to CD for indefinite retention.

    The point is, backup strategies depend upon the requirements of your application and your environment. Think about some scenarious that could occur, including "worst case" disasters, and try to come up with a backup strategy that will help you recover from them quickly.

    One last imperative: TEST YOUR BACKUP STRATEGY REGULARLY. You need to actually go through the steps required to recover from the disaster scenarios you planned for, and document the steps. This way, you know they will work and you know you can do them quickly, efficiently, and accurately should an emergency occur.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Apr 2004
    Location
    The Netherlands
    Posts
    29

    Howto work and manage backup log files

    Thanks Blindman,

    After reading your reply I figured out how things works. So I will now gonna test it.

    Marc,

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •