Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2005
    Posts
    91

    Unanswered: SQL Server 2000 - Proper Backup Procedures

    Hi everyone I am fairly new to SQL server and need some help with creating backups. I am a little confused whether to append or overwrite my backup data when saving full, differential and transaction log backups to disk. Below is my current backup schedule.

    1) A full backup every night at 2:00a.m. that overwrites my existing backup. Should this be an appended backup? Wouldn't my file keep growing? How do I manage the backup file size?
    2) Differential backups every two hours that is set to append. How do I stop this file from growing? Should I be controlling the size of my backups through the backup set expiry option?

    How do I incorporate transaction logs into my backup schedule and manage the size of the log? Should the log backups also be appended or overwriten?

    I appreciate everyones help in advance.

  2. #2
    Join Date
    Jan 2006
    Location
    USA
    Posts
    115
    1. I need to know that which device you used to back up your database..?
    2. What is the existing database size..? is it the sensitive data..?

    And if possible, give the growth rate of your database [daily or weekly or monthly] - it will helps to establish precise plan for database back up.
    Rajesh Patel

    Everybody says - mistake is the first step of success, but it's not true. The correction of the mistake is the first step of success.

  3. #3
    Join Date
    Nov 2005
    Posts
    91
    I am currently backing up my backups onto disk.

    The current size of my database is 50G and the transaction log is about 20G. Yes the data is sensitive but we do have downtime from about 3am till about 6am.

    Is there any way to cut down the size of the log file. I know that I first have to run a complete backup but I am unsure as to how to truncate the log. Can this be done without using T-SQL?

    Hope this info helps and thanks in advance.

  4. #4
    Join Date
    Jan 2006
    Location
    USA
    Posts
    115
    1. You need to setup your recovery model according to your requirement. This will helps you to manage your transaction log size. For his you should refer "SQL Server Books Online", write 'recovery models' under Index tab.

    2. As you said your server is not busy between 3am till about 6am. If complete database backup is completed in between this time as well as if you have spare backup storage space, it's nothing wrong if you take complete database backup on daily basis.

    3. If you are going to take complete database backup on daily basis than take Transaction Log backup 4 to 8 times per day according to your requirement.

    4. If you plan complete backup once in a week then take differential backup once or twice in a day, and then take transaction log backup for each two hours or according to your requirements.

    5. As you're taking your backup on a disk, it's good practice to maintain more than one copy of your database backup on another device.

    6. Make your own database maintenance plan.

    but I am unsure as to how to truncate the log. Can this be done without using T-SQL?
    Using Enterprise Manager you can manage all types of backups.

    Right click on your database -> All Task -> Backup Database
    Check option "Remove inactive entries from transaction log" from Option tab.
    Rajesh Patel

    Everybody says - mistake is the first step of success, but it's not true. The correction of the mistake is the first step of success.

  5. #5
    Join Date
    Sep 2002
    Location
    Sydney, Australia
    Posts
    255

    Omg

    Quote Originally Posted by dsmbwoy
    The current size of my database is 50G and the transaction log is about 20G.
    So the cancerous tumour is growing. Soon the log size will eclipse the db size. I love it.
    Derek Asirvadem
    Senior Sybase DBA/Information Architect derekATsoftwaregemsDOTcomDOTau
    Anything worth doing is worth doing Right The First Time
    Spend your money on standards-compliant development or spend 10 times more fixing it

  6. #6
    Join Date
    Jan 2006
    Location
    USA
    Posts
    115
    Quote Originally Posted by DerekA
    So the cancerous tumour is growing. Soon the log size will eclipse the db size. I love it.
    By selecting & setting up appropriate options, log size can be manage.

    Not a big issue, SQL Server is providing lots of option for reduce high log size.
    Rajesh Patel

    Everybody says - mistake is the first step of success, but it's not true. The correction of the mistake is the first step of success.

  7. #7
    Join Date
    Dec 2002
    Location
    Sunny Florida
    Posts
    121
    Like differential and trans log backups. I've never seen a 20GB log file that wasn't done by accident!

  8. #8
    Join Date
    Jan 2006
    Location
    USA
    Posts
    115
    Quote Originally Posted by DBA-ONE
    I've never seen a 20GB log file that wasn't done by accident!
    Even I have never seen 20GB log file [where database size is 50GB].

    We can shrink transaction log, but the differential and trans log backups is the best way that reduce transaction log size. Many times combination of the different option will produce good result.
    Rajesh Patel

    Everybody says - mistake is the first step of success, but it's not true. The correction of the mistake is the first step of success.

  9. #9
    Join Date
    Dec 2002
    Location
    Sunny Florida
    Posts
    121
    This is what I do. The only variable is the frequency in which these things happen. This depends on the use of the database from my experience.

Posting Permissions

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