1

I've backed up a database on SQL Server 2012 that resulted in a file of 33MB in size. Then, I restored that backup on SQL Server 2016 and created another backup, but this time on SQL Server 2016.

The size of the backup changed from 33MB (SQL Server 2012) to 315MB (SQL Server 2016).

Why such big increase to the size of the backup file on the SQL Server 2016?

Appreciate any help.
Wesley

  • 2
    Was only the SQL 2012 backup a compressed backup? Did you accidentally append several backups into the same file? – David Browne - Microsoft Jun 11 at 20:44
2

Sometimes your backup device can be bigger in comparison to the previous version for reasons like:

  1. Your transaction log increased, check your log to validate if you have not grown it.
  2. You could have extra features enabled in your new instance that are not in the previous instance. i.e: if you have used Full Text-search in your SQL 2016. One time a customer database backup got exponential growth for this.
  3. Your backup device on the new SQL Server version is not initialized {NON Init | Init} in other words your backup file appends the backup into the same file each time that you performed a backup.

    --Media Set Options
      { NOINIT | INIT }
    | { NOSKIP | SKIP }
    | { NOFORMAT | FORMAT }
    

More details here.

| improve this answer | |
1

By default SQL Server compress backup option is not checked.

Server properties

So if your backup script is as simple as

BACKUP DATABASE YourDB TO DISK = 'C:\BackupSQL\YourDB.bak';

on the older server (assuming this option was checked) you'd have a compressed backup while on the new server the same command would generate a larger backup file without compression.

| improve this answer | |
  • 1
    I would bet on that one. Your 2012 instance probably have compression set as default and the new one doesn't. Run this on both instance and validate : select value_in_use from sys.configurations where name ='backup compression default' – Dominique Boucher Jun 12 at 18:22

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.