Sample Header Ad - 728x90

How to backup without compression?

0 votes
3 answers
514 views
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
enter image description here I get it as compressed . is there a way, without changing the settings, to get it non-compressed?
Asked by Marcello Miorelli (17274 rep)
May 7, 2025, 11:37 AM
Last activity: May 8, 2025, 01:47 PM