How to backup without compression?

Question

when I take a diff backup of my database without specifying compression It still does compress.

what if I explicitly want without compression?

BACKUP DATABASE MyDatabase TO DISK='\\myserver\SQLBackups$\SQLBackups\Non_Prod\dev_server\mydatabase\DIFF\myserver_myDatabase_DIFF_20250504_060118__.bak'
WITH  FORMAT,INIT, STATS=1,BLOCKSIZE=4096,differential,COPY_ONLY

when I run this query:

    SELECT  
     A.Backup_finish_date
    ,BackupSize_GB              = CAST(COALESCE(ISNULL(a.compressed_backup_size,A.BACKUP_SIZE),0)/1024.00/1024.00/1024.00 AS NUMERIC(18,2)) 
    ,BackupSize_MB              = CAST(COALESCE(ISNULL(a.compressed_backup_size,A.BACKUP_SIZE),0)/1024.00/1024.00 AS NUMERIC(18,2))
    ,[Backup Duration]          = (CAST(DATEDIFF(s,A.backup_start_date,A.backup_finish_date) AS int))
    ,A.type
    ,F.physical_device_name
    ,A.is_copy_only
    ,A.media_set_id
    ,A.backup_start_date
    ,A.database_name
    ,A.has_backup_checksums
    ,F.physical_block_size
    ,is_compressed = CASE WHEN ISNULL(a.compressed_backup_size,A.BACKUP_SIZE) = ISNULL(a.compressed_backup_size,-1) THEN 1 ELSE 0 END
            from msdb.dbo.backupset A WITH(NOLOCK)
INNER JOIN msdb.dbo.backupmediafamily F WITH(NOLOCK)
        ON A.media_set_id = F.media_set_id
where a.backup_finish_date >= DATEADD(DAY,-30,SYSDATETIME())
AND database_name = 'MyDatabase'
and has_backup_checksums = 0

I get it as compressed.

is there a way, without changing the settings, to get it non-compressed?

Answer 1

From the docs

NO_COMPRESSION

Explicitly disables backup compression.

Linked in that same section is the page on setting Server configuration: backup compression default.

Answer 2

You might want to try the option NO_COMPRESSION as documented in BACKUP (Transact-SQL) (Microsoft Learn | SQL)

general WITH options

<general_WITH_options> [ ,...n ]::=
--Backup Set Options
   COPY_ONLY
 | [ COMPRESSION [ ALGORITHM = { MS_XPRESS | accelerator_algorithm } ] | NO_COMPRESSION ]
 | DESCRIPTION = { 'text' | @text_variable }
 | NAME = { backup_set_name | @backup_set_name_var }
 | CREDENTIAL
 | ENCRYPTION
 | FILE_SNAPSHOT
 | { EXPIREDATE = { 'date' | @date_var }
        | RETAINDAYS = { days | @days_var } }
 | { METADATA_ONLY | SNAPSHOT }

So your statement would look like this:

BACKUP DATABASE MyDatabase 
TO DISK='\\myserver\SQLBackups$\SQLBackups\Non_Prod\dev_server\mydatabase\DIFF\myserver_myDatabase_DIFF_20250504_060118__.bak'
WITH  
    FORMAT,
    INIT, 
    STATS=1,
    BLOCKSIZE=4096,
    DIFFERENTIAL,
    NO_COMPRESSION, -- <-- this option here
    COPY_ONLY;

Answer 3

I found out the NO_COMPRESSION on this comprehensive question:

--DBCC TRACEON (3213, 3605, -1);
BACKUP DATABASE [Test] TO
      DISK =  'NUL:'
     --,DISK = 'NUL:'
     -- DISK =  'BackupTest1.bak'
     -- ,DISK =  'BackupTest2.bak'
WITH
    STATS = 5,
    FORMAT,
    CHECKSUM,
    NO_COMPRESSION,
    COPY_ONLY
    --,BUFFERCOUNT = 40
    --,MAXTRANSFERSIZE = 4194304--2097152,
    --,BLOCKSIZE = 16384
--DBCC TRACEOFF (3213, 3605, -1);

there is always the settings that you can look at: (without changing)

    EXEC sys.sp_configure N'backup compression default'

Here if you want to change that setting.

My own query is not correct, here is the correct version:

    SELECT  
     A.Backup_finish_date
    ,BackupSize_GB              = CAST(COALESCE(ISNULL(a.compressed_backup_size,A.BACKUP_SIZE),0)/1024.00/1024.00/1024.00 AS NUMERIC(18,2)) 
    ,BackupSize_MB              = CAST(COALESCE(ISNULL(a.compressed_backup_size,A.BACKUP_SIZE),0)/1024.00/1024.00 AS NUMERIC(18,2))
    ,[Backup Duration]          = (CAST(DATEDIFF(s,A.backup_start_date,A.backup_finish_date) AS int))
    ,A.type
    ,F.physical_device_name
    ,A.is_copy_only
    ,A.media_set_id
    ,A.backup_start_date
    ,A.database_name
    ,A.has_backup_checksums
    ,F.physical_block_size
    ,is_compressed = CASE WHEN ISNULL(a.compressed_backup_size,A.BACKUP_SIZE) = ISNULL(A.BACKUP_SIZE,-1) THEN 0 ELSE 1 END
    ,a.compressed_backup_size
    ,A.BACKUP_SIZE
    ,a.encryptor_thumbprint
    ,a.encryptor_type
        from msdb.dbo.backupset A WITH(NOLOCK)

INNER JOIN msdb.dbo.backupmediafamily F WITH(NOLOCK)
        ON A.media_set_id = F.media_set_id
where a.backup_finish_date >= DATEADD(DAY,-30,SYSDATETIME())
AND database_name = 'ZurichTrace'
and has_backup_checksums = 0

Other ways of finding if the backup is compressed.

Compression ratio after you enabled TDE

Number of files, number of CPUs - backup performance

Here the results (after I took a NON_COMPRESSION backup: