On a Windows server 2012 R2 with MSSQL 2012 SP4 Express, installed with all the default options, I do daily backups of a small database with a scheduled task running the backup command with default options. The database sees very little activity, maybe a dozen inserts a day in a specific table which has no indexes other than the PK. The recovery model is Full.
Yesterday's backup was 33mb and today's backup is 11mb. Auto-shrink is disabled, backup compression is disabled, and if I restore yesterday's backup then re-back it up, I get a 11mb file. I've verified we didn't lose any data and everything is still there. I also tried restoring and re-backing up on my PC running MSSQL2014 and I get the same result (33mb to 11mb).
I don't see any recent updates for MSSQL on the server nor on my machine, and I can't find any evidence of this behavior online. Is there some other process which could explain this? Not that I'm complaining mind you, I just find it weird and I'd rather understand what happenned.
Edit #1 -for LowlyDBA
The backup command is:
DECLARE @pathName NVARCHAR(512)
SET @pathName = 'D:\bkp\my_DB_' + REPLACE(Convert(nvarchar(20), GETDATE(), 120),':','-') + '.bak'
BACKUP DATABASE my_DB TO DISK = @pathName
results of sp_spaceused
backup:
database_name database_size unallocated space
my_DB 12.50 MB 0.13 MB
reserved data index_size unused
11136 KB 8584 KB 1936 KB 616 KB
current:
database_name database_size unallocated space
my_DB 13.50 MB 0.99 MB
reserved data index_size unused
11272 KB 8592 KB 1960 KB 720 KB
sp_spaceused
on the old vs current databases and show us the results? If you're just doing a once a day backup, why not useSIMPLE
recovery mode? Do other people have full write access to this database? Also include the backup command you run.