Post reply

SQL Server 2008R2 - Backups

  • January 10, 2011 at 10:18 am

    #230271

    Hello All,

    We have some big databases where we are running backups on Full Recovery Modes.

    The problems arises with the backup file size. They keep growing and are quite huge. Any suggestions on how i can compress them??

    Thanks!

  • January 10, 2011 at 10:38 am

    #1271508

    Database backups or log backups?

    Are the databases growing?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 10, 2011 at 10:43 am

    #1271513

    Thank you for the quick response. The databases are fine, its just the .bak and Transaction Log file size. The data drive where we store them becomes difficult to access with the growing file size and since the transactions are very important we cannot clear the history every now and then. I am attaching a screen shot.

  • January 10, 2011 at 10:46 am

    #1271517

    Hi,

    Starting with R2 Standard Edition, you could compress the backup by selecting compress backup in the drop down option near the bottom of the backup window or use TSQL.

    Here's a page explaining more how to do it step by step: http://msdn.microsoft.com/en-us/library/ms187510.aspx

    You could compressed both Full and Transaction logs backups.

    Hope this help!!

    Harry

  • January 10, 2011 at 10:51 am

    #1271522

    How are you taking your backups? What command?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 10, 2011 at 11:07 am

    #1271529

    Thanks, I did not know about the Compress option. I am going to test it on a development server to see the difference.

    I am taking backup using maintenance plans.

    These are the steps i am using to Backup the Database:

    1. Rebuild Index Task

    2. Check Database Integrity Task

    3. Full Database Backup

    4. Transaction Database Backup

    5. History Cleanup

    6. Maintenance Cleanup Task

    Thanks!

  • January 10, 2011 at 12:20 pm

    #1271559

    Are you appending to the same backup file?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

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

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