Post reply

Native backup size rule of thumb

  • July 9, 2019 at 7:29 am

    #3661013

    Does anybody have a reliable system for judging how big a compressed native full backup  of an OLTP database is likely to be compared to the size on disk? I know that there are a lot of variables that make it hard to say, but is there finger in the air a rule of thumb? (Sorry for the awful mixed metaphor.)

    Can I look across an estate and say if there are 10,00 databases with an average size of 500 GB each, I'll need 10,000 * 500 GB * [?] disk space to get one full backup.

    Thanks.

     

     

  • July 9, 2019 at 11:09 am

    #3661070

    None that I'm aware of. I'd suggest testing on a few disparate data sets, and generate a local one. The compression is consistent, but as you say, very dependent on the data in question.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • July 9, 2019 at 11:35 am

    #3661080

    Thanks. I've done some sampling, but I hoped there might be a broad rule I could use estate-wide. Never mind, at least I've had it confirmed.

  • July 9, 2019 at 11:37 am

    #3661081

    Ha! You're placing way more confidence in my answer than I do.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • July 9, 2019 at 12:18 pm

    #3661085

    I agree with Grant, it depends on the data in databases and even features/versions you use.

    For example, TDE makes compression equal to 0% (zero) on versions before sql 2016.

     

  • July 9, 2019 at 1:47 pm

    #3661177

    In my experience, the size of the backup is reduced to about 1/6 that of a non-compressed backup.

    It does, of course, depend on the contents of your database. If you are storing things in already compressed columns or rows or storing jpegs there is going to be less compression on the backup.

  • July 9, 2019 at 3:42 pm

    #3661236

    If you  have 10,000, use sampling. If you know a representative set, use that, otherwise, grab 20-50 dbs, do the backups, and make an educated guess. I assume if you have 10k, they're all similar.

     

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • July 9, 2019 at 3:53 pm

    #3661240

    Steve Jones - SSC Editor wrote:

    If you  have 10,000, use sampling. If you know a representative set, use that, otherwise, grab 20-50 dbs, do the backups, and make an educated guess. I assume if you have 10k, they're all similar.  

     

    They can even be backed up to NUL device with copy_only and compression.

    Check msdb.dbo.backupset.compressed_backup_size  and backup_size columns to calculate the ratio you got.

    backup database model to disk='NUL' with copy_only,compression,description='test compression',stats=1;

  • July 17, 2019 at 11:38 am

    #3663712

    I hadn't spotted that there were more answers; thanks, all.

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

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