Need MS SQL 2016 MAX Compression Backup command

Leo Torres
Leo Torres used Ask the Experts™
on
I looked online I cant seem to find the MS SQL 2016 backup command with "MAX" compression.

I have seen plenty statements with compression Keyword but none of them have the level specified 0-10 or 4 not even sure what the max is but thats what I am looking for right now.

Native SQL backup with max compression for SQL server 2016.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
lcohanDatabase Analyst

Commented:
I do not think that to date there is a direct way/parameter to use and directly control the SQL Server Native backup compression ratio but only to check how good(or poor) that is by running at T-SQL like below:

SELECT backup_size/compressed_backup_size FROM msdb..backupset;  

Open in new window

Author

Commented:
OK so that number is 15,151,636,512.

Is that in KB?
lcohanDatabase Analyst

Commented:
Not really and not sure why is that high as it should be the result of the division between backup_size/compressed_backup_size so it shows how much smaller the compressed backup is. Please run updated statement below where replace the YourDBnameHere with one actual DB name you're looking after.
SELECT max(backup_size/compressed_backup_size) FROM msdb..backupset where database_name = 'YourDBnameHere' and type = 'D'; 

Open in new window

11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Author

Commented:
same result.

FYI
in 2016 the column name is: compressed_backup_size

I guess that's the compression size the value for backup size is: 60,137,946,112 I am guessing 60 gigs

the database file size is 139 gigs. So 15 gigs is not so bad, if that is the value for 15,151,636,512.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
Yes, the  compression size is in bytes (not kb).

SQL Server doesn't have levels of compression.  Some third party tools, such as Redgate, do have that.

Author

Commented:
Well a completed backup with compression completed and created a 50 gig bak file? based on the query from Icohan should it be closer to 15 gigs?
lcohanDatabase Analyst

Commented:
Gents, that number above should be result of a division between backup_size/compressed_backup_size right?
So I have no idea where from the "15,151,636,512" above came and something is not 100% accurate here.
https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/backupset-transact-sql?view=sql-server-2016
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
Well a completed backup with compression completed and created a 50 gig bak file?

Is there a long-running, large transaction running?  In theory that could cause a very large backup file.

Author

Commented:
This database has not been touched in months and highly unlikely any transactions are open.

Author

Commented:
Also to be clear Icohan

if you follow your link i am providing the value in the field: compressed_backup_size

This field appears on the field list towards the bottom. I did not preform any mathematical operations(division or multiplication to be clear.)

compressed_backup_size 	Numeric(20,0) 	Total Byte count of the backup stored on disk.

To calculate the compression ratio, use compressed_backup_size and backup_size.

During an msdb upgrade, this value is set to NULL. which indicates an uncompressed backup.

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial