I'm new to SQL server and need your advice.

I have a database that shows 188GB as the size in the database properties window.

When I take a backup of the database, that backup file is only 65GB. The database properties window still shows the database itself is 188GB.

Why is there a difference in the size of the backup compared to the actual database? I'm not using backup compression.

  • Are you using backup compression? – Erik Darling Apr 19 '17 at 15:40
  • no, i just used normal full backup. didn't compress – Dash Apr 19 '17 at 15:41

Check to see the amount of space actually used in your database. You probably have a lot of free space available. A quick way to do this would be to connect to your database and use exec sp_spaceused which will give you the database size and unallocated (unused) space.

When SQL Server backs up the database it only needs to backup the pages that contain data (and the log), which means that the backup can easily be smaller than the files themselves.

This question has also been answered in Why is a .bak so much smaller than the database it's a backup of?

  • Yes, a lot of free space. Thank you so much – Dash Apr 19 '17 at 15:57

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