none
SQL Server database backups RRS feed

  • Question

  • Hi All,

    this is my first time attempting to set up backup jobs for databases. I have some questions and greatly appreciate if someone can point me in the right direction.

    1) How much space needs to be allocated for the backup on the server ( i am planning to set up backups for two databases 450gb and 600gb).

    2) What kind of backup is recomended?

    3) How to calculate the compression?

    4) How many days of backups need to be retained.

    Thanks 

    LKruger

    Wednesday, May 1, 2019 2:02 PM

Answers

  • Hi Lisa,

    Depending on the version and edition of SQL, the SQL server should be capable of compressing backups.  You can either set this at the time the backup is taken OR if you want it to be default for backups then you can set this in the instance.  If you right click on the instance name in SSMS and go to properties, there is an option there (I think its under the advanced section) for default compression for backups.

    If backups are compressed, then the .bak file size is typically around 1/5 of the size of the original database from my experience.  However, you should ensure there is more than this and monitor it for the first few backups running.

    For the number of backups to be retained, this really depends on your business needs.  Do you need to retain data historically for compliance?  If there are no instructions for this then I typically see 7 days if you backup to disk, or up to 30 days if you use an external backup server that uses compression and deduplication. But really, this is up to your business for how long you need/want to retain backups for.

    Let me know if you have any further questions.

    Thanks,

    Matt  


    Wednesday, May 1, 2019 6:14 PM
  • Hi LKruger,

    >> How much space needs to be allocated for the backup on the server ( i am planning to set up backups for two databases 450gb and 600gb).
    For compressed backups, the size of the final backup file depends on how compressible the data is, and this is unknown before the backup operation finishes. To allow the backup file to grow only as needed to reach its final size, use trace flag 3042. Trace flag 3042 causes the backup operation to bypass the default backup compression pre-allocation algorithm. Please refer to Allocation of Space for the Backup File.
    >> What kind of backup is recomended?
    Any backup is based on a full backup, if the database is large, you can supplement a full database backup with a series of differential database backups. We recommend you take log backups frequently, both to minimize work loss exposure and to truncate the transaction log. But really, this is up to your demand.
    >> How to calculate the compression?
    To calculate the compression ratio of a backup, use the values for the backup in the backup_size and compressed_backup_size columns of the backupset history table. Please refer to Calculate the Compression Ratio of a Compressed Backup.
    >> How many days of backups need to be retained.
    It depends on your demand. To have the backup set expire after a specific number of days, click After (the default option), and enter the number of days after set creation that the set will expire. The default value is set in the Default backup media retention (in days) option of the Server Properties dialog box (Database Settings Page). Please refer to Backup Options Page.

    Best regards,
    Cathy Ji

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by LisaKruger Friday, May 3, 2019 2:48 PM
    Thursday, May 2, 2019 3:07 AM

All replies

  • Hi Lisa,

    Depending on the version and edition of SQL, the SQL server should be capable of compressing backups.  You can either set this at the time the backup is taken OR if you want it to be default for backups then you can set this in the instance.  If you right click on the instance name in SSMS and go to properties, there is an option there (I think its under the advanced section) for default compression for backups.

    If backups are compressed, then the .bak file size is typically around 1/5 of the size of the original database from my experience.  However, you should ensure there is more than this and monitor it for the first few backups running.

    For the number of backups to be retained, this really depends on your business needs.  Do you need to retain data historically for compliance?  If there are no instructions for this then I typically see 7 days if you backup to disk, or up to 30 days if you use an external backup server that uses compression and deduplication. But really, this is up to your business for how long you need/want to retain backups for.

    Let me know if you have any further questions.

    Thanks,

    Matt  


    Wednesday, May 1, 2019 6:14 PM
  • Hi LKruger,

    >> How much space needs to be allocated for the backup on the server ( i am planning to set up backups for two databases 450gb and 600gb).
    For compressed backups, the size of the final backup file depends on how compressible the data is, and this is unknown before the backup operation finishes. To allow the backup file to grow only as needed to reach its final size, use trace flag 3042. Trace flag 3042 causes the backup operation to bypass the default backup compression pre-allocation algorithm. Please refer to Allocation of Space for the Backup File.
    >> What kind of backup is recomended?
    Any backup is based on a full backup, if the database is large, you can supplement a full database backup with a series of differential database backups. We recommend you take log backups frequently, both to minimize work loss exposure and to truncate the transaction log. But really, this is up to your demand.
    >> How to calculate the compression?
    To calculate the compression ratio of a backup, use the values for the backup in the backup_size and compressed_backup_size columns of the backupset history table. Please refer to Calculate the Compression Ratio of a Compressed Backup.
    >> How many days of backups need to be retained.
    It depends on your demand. To have the backup set expire after a specific number of days, click After (the default option), and enter the number of days after set creation that the set will expire. The default value is set in the Default backup media retention (in days) option of the Server Properties dialog box (Database Settings Page). Please refer to Backup Options Page.

    Best regards,
    Cathy Ji

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by LisaKruger Friday, May 3, 2019 2:48 PM
    Thursday, May 2, 2019 3:07 AM
  • Thanks Matt and Cathy that was very informative. One more qwuestion. is it advisable to buy a 3rd part y tool for back ups or use SSMS

    Thanks

    Friday, May 3, 2019 2:49 PM
  • Hi Cathy,

    You need a reliable way to run and monitor your backups. If you do this via SSMS and the SQL Agent job isnt running then that backup job won't run - and you wont get any alerts to say that it hasn't ran (because alerts are controlled by the agent jobs).  So you would have to monitor several things to do it in this way (but it is a valid way to back things up).

    Azure Backup has support for SQL on VMs (which is what I assume you are using?).  Now, this isn't cheap compared to the standard VM backups it can do, but it is a managed service.  

    So my advice would be to use Azure Backup for this if it is in budget.

    Thanks,

    Matt

    Friday, May 3, 2019 3:22 PM