SQL Server – Why Are SQL Server Backups Suddenly Smaller?

backupsql serversql server 2014sql-server-2012

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

Best Answer

My first guess was wrong so I deleted that answer, the OP simply made 3 backups in the same file, while the last backup was the only backup in the file.

To avoid this issue in future, add with init in your backup command, so even if you press F5 two times at the same minute (your backup file has datetime in its name so it seems that you cannot have more than 1 backup in one file, but your database size is very small so the backup is nearly instant), the first backup will be overwritten with the second and there will be only 1 backup in 1 file.

Related Question