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?
I get it as compressed .
is there a way, without changing the settings, to get it non-compressed?
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

Asked by Marcello Miorelli
(17274 rep)
May 7, 2025, 11:37 AM
Last activity: May 8, 2025, 01:47 PM
Last activity: May 8, 2025, 01:47 PM