We have the following setup:

  • Once a week (Sunday at midnight), a full database backup is performed. A new BAK file is created for this purpose, let's call it Backup1.bak.
  • From then, a transactional backup is performed every hour. This is appended to Backup1.bak.
  • Once a day at midnight (except Sundays) a differential backup is performed. This is also appended to Backup1.bak.
  • The following Sunday at midnight, Backup1 is zipped and moved to external media, a new full backup is created and is placed in Backup2.bak. Backup1.bak is deleted.

The database is running in full recovery mode, and all backups only operate on one database in one instance.

This works well in SQL Server 2008. However, we are now starting to roll out SQL Server 2012 implementations, and the problem we are encountering is that the BAK file is growing exponentially. A typical MDF file size rarely exceeds 15Gb and LDF rarely exceeds 50Gb, but the BAK file is sometimes as big as 900Gb by Saturday. This is naturally causing capacity constraints on client sites with 1Tb HDDs.

Is there a different way of doing transactional and differential backups in 2012 as compared to 2008?

  • 2
    Run sp_configure on both instances, and search for the value of "backup compression default". Is it possible that the default on 2008 is 1 but 0 on 2012? Commented Feb 18, 2014 at 12:14
  • Hi Matan, the value of "backup compression default" is 0 on both 2008 and 2012.
    – staterium
    Commented Feb 19, 2014 at 6:07

1 Answer 1


Did you use INIT as a parameter to the backup command?

Otherwise, the dump is appended to the backup file.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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