Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2011
    Posts
    2

    Unanswered: backup/truncate ?

    Hello, I just wanted to double check and see if I am doing this right or I am doing unneccessary backups/logs/truncate stuff.

    I am going to abandon my Backup Exec SQL backups and just do it by a SQL script.

    If I do a script that does:

    DATABASE [SQLDB] TO DISK

    BACKUP LOG [SQLDB] TO DISK

    BACKUP LOG [SQLDB] with truncate_only
    dbcc shrinkfile(SQLDB_log)


    Should I do every DB this way or do I really only need to do a DB backup and then truncate the logs after that?

    Thanks

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Backup your database, nothing more. Don't monkey with the logs, shrink the database, or anything else as a daily "care and feeding" task because there is little chance any of that will help you, and significant chance that it will hurt you.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jan 2011
    Posts
    2
    ok so just do DATABASE [SQLDB] TO DISK and skip the BACKUP LOG

    The reason why I am truncating the LDF is because I got some LDFs that are 23GB etc....

    Now I am assuming since you said to skip the Backup Log that if I would of started just doing DATABASE [SQLDB] TO DISK form the get go and not depend on Backup Exec SQL Agent, that I would of never gotten such a huge LDF file and I would of never had to do a truncate of the LDF.

    Is the above statement correct?
    Thanks

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    There's no way that I can give detailed answers about your backup strategy without know a LOT more than you've posted. There are too many ways to configure the backup process and tailor it to the needs of a specific server/installation, so there are too many ways to run into subtle "gotchas" when making sweeping statements.

    In general if you make normal database backups, the SQL Server system will take care of itself. This is how the system is designed, and how it normally works.

    I'd VERY STRONGLY suggest that you review the SQL Server Maintenance Plans to get some insight into what you can do, why you want to do it, and how to make it happen!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    When Pat said "backup your database...don't monkey with the logs", he meant for you to continue doing full and tlog backups, not to stop tlog backups altogether. But you definitely need to quit those "truncate_only", and the shrinking business. You need to properly size all devices of the database, so that not only you don't have to shrink/expand on a daily basis, but rather have a solid and verifiable understanding as to when you will actually need to adjust the device size (preferrably in at least 6 months, if not a year).
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by Pat Phelan View Post
    Backup your database, nothing more. Don't monkey with the logs,
    Pat, I hope I am misinterpreting you. Most likely, he should be backing up his log files on a production system.
    If it's not practically useful, then it's practically useless.

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

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Yes, rdjabarov translated what I meant nicely. Do full backups, frequently. Do log backups as needed. A few applications don't permit log backups (for example, SQL Server itself won't permit log backups of the master database), some systems don't require log backups, but log backups are certainly a fine idea if your system will tolerate them and most systems will.

    Backup your database (full or log) every time you've done more work than you are willing to redo in the event of a system failure. In most cases I do a full database backup daily and a log backup hourly. but you need to tailor this to suit your system.

    Do not regularly shrink, truncate, or otherwise tamper with your SQL log files. These changes should be significant systems changes, done only with deliberation, documentation, care, and planning.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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