October 24, 2019 at 3:02 pm
Compression ratio of compression backup depends on the data that has been compressed. I agree variety of factors can impact. Major factors include.
The type of data i can think of, unless you have more to add other than below.
Character data compresses more than other types of data. If there are images then it won't compress much.
Encrypted data compresses significantly less than equivalent unencrypted data. If transparent data encryption is used to encrypt an entire database, compressing backups might not reduce their size by much, if at all.
Just checking the data is coming from other db system want to estimate the backup space, i am thinking on average the backup compression would be approx 50% to database data file right, let me know if you don't agree? Again i agree it depend on the factors and type of data. Does SQL Server backup compression does more than other db system or they almost same? Thanks in advance!
October 24, 2019 at 9:25 pm
It also depends on how "full" your database is. A 400 GB database file that has 1 MB in use will compress most of that. We have a database that is 360 GB which we recently archived most of the data as it was unused and now have 322 GB free on that. When we back it up, we are using maximum compression and the FULL backup is just over 2 GB. This is likely not a normal case, but is a real-world scenario. We are also using RedGate SQL Backup to do our backups, but I expect that native SQL backup will give similar results. The database being backed up here is a mix of CHAR, INT, GUID, and DATETIME datatypes in the majority of the tables tables with a VERY small number of tables storing XML datatypes. There is not much if any VARCHAR's. There are nearly 900 views and over 27,000 stored procedures and Service Broker is configured on this database and over 2000 tables.
If you are asking with the intent of saving space on your backup drive, my personal recommendation is to make it as big as, if not larger, than the size of all of your databases combined. This gives you space to store multiple backups and gives you room for data growth. Last thing you want is for a backup to fail due to lack of disk space.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
October 25, 2019 at 1:45 am
Instead of guessing, why not just do a test?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply