Post reply

Backup methodology

  • October 17, 2005 at 10:21 pm

    #89687

    Hi

    I need to plan for a backup methodology for a production OLTP database. My plan is to take a full backup daily and log file backup 4 hourly. But I am afraid whether the log file become full or not. So my backup plan should include the log file truncate also. But I am confused about at what time I should truncate the log file and how? So can you guys please advice me asap...

    I am planning to use database maintenance plan backing up  database and log file . I could't able to see any option for logfile truncation. So can you guys tell me how can I truncate/shrink the log file using the management plan options??

    Please help me....

    Thanks in advance

    regards

    Santhosh

  • October 17, 2005 at 10:55 pm

    #597822

    The interval of the transaction log backup should be determined by the business users. How much data can they afford to lose? If they can't afford to lose more than 15mins of data, then you 4 hourly backup plan is not enough.

    If the transaction log is set to auto-grow you won't have any problems with it becoming full. Unless you're experiencing a large amount of long transactions, which is unlikely in an OLTP environment, the transaction log shouldn't grow too much, if at all, between each backup.

    The backup plan shouldn't include a truncate, better to include a DBCC SHRINKDATABASE and/or DBCC SHRINKFILE

     

    --------------------
    Colt 45 - the original point and click interface

  • October 17, 2005 at 11:52 pm

    #597831

    Dear Phill Carter

    Thanks for the reply. The user is agreeable for the 4 hours log backup.

    The log file is already set to auto-grow. But sometimes in the future, it may become full. So need to shrink/truncate it, right?

    My question is, how can I do this truncate command, DBCC SHRINKDATABASE and/or DBCC SHRINKFILE , if I use the SQL SErver supplied management plan? I could't able to see any option in the managment plan to do this, Any advice?

    regards

    santhosh

  • October 18, 2005 at 12:37 am

    #597835

    " ... But sometimes in the future, it may become full. So need to shrink/truncate it, right?  ... "

    As per my previous post, "Unless you're experiencing a large amount of long transactions, which is unlikely in an OLTP environment, the transaction log shouldn't grow too much, if at all, between each backup"

    I wouldn't use the generic maintenance plans unless you have a specific maintenance window where the database is offline. Doing things like index rebuilds using the maintenance plans is an all-or-nothing approach that can have a significant performance impact on your system. Much better to have a flexible maintenance system that only performs the maintenance that is necessary.

     

    --------------------
    Colt 45 - the original point and click interface

  • October 18, 2005 at 12:46 am

    #597836

    Dear Phill Carter

    Thanks for the reply.

    What is the other method that I can adopt to do the daily backup of the user databases and doing the log truncation? It is by using (a) Backp database option in the Enterprise Manager (b) Using the Jobs option

    Can you please help me

    Regards

    Santhosh

  • October 18, 2005 at 12:54 am

    #597838

    Take a look in the script library,

    http://qa.sqlservercentral.com/Scripts/

    There are a number of scripts that provide backup functionality, pick one that suits your needs the best.

     

    --------------------
    Colt 45 - the original point and click interface

  • October 18, 2005 at 6:57 am

    #597912

    Hi

    One more question, if I do a backup using backup command like 'BACKUP DATABASE PUB To DISK=.....' how can I include the verification?

    Regards

    Santhosh

  • October 18, 2005 at 8:00 am

    #597930

    This is very simple, check Books Online.  Under the "Backing Up and Restoring Databases" topic there is a section headed "Managing Backups" which details how to verify a backup.

     

    --------------------
    Colt 45 - the original point and click interface

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply