1

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
  • 1
    Can you run 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 use SIMPLE recovery mode? Do other people have full write access to this database? Also include the backup command you run. – LowlyDBA Feb 14 '18 at 22:17
  • In some cases, full backup can ignore the ldf file as the mdf file contain the actual data and hence, the backup file can be smaller than the combined size of ldf and mdf files. If your question is the size of backup of yesterday was greater the backup size of today without any shrink and compression operations, better check whether there were some kind of DML activities going on. – Asteway Feb 15 '18 at 2:06
  • Could anyone else have access to the db? – Sir Swears-a-lot Feb 15 '18 at 8:41
  • Can it be that your script was running more then one time and put 3 backups instead of one into the same file? Please check restore headeronly from disk = 'your path to backup of 33Mb' if it has only one row ? – sepupic Feb 15 '18 at 15:18
  • @sepupic Well I'll be! There is indeed 3 rows. Mystery solved, post it as an answer? – 0xFF Feb 15 '18 at 15:22
2

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.

  • Thanks for the tip, though as I commented on the now-deleted answer these backups are only a temporary situation. Still, it's good to know! – 0xFF Feb 15 '18 at 15:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.