Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
1  
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? –  Matan Yungman Feb 18 at 12:14
    
Hi Matan, the value of "backup compression default" is 0 on both 2008 and 2012. –  Ravish Feb 19 at 6:07

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.