Sql-server – SQL Server 2012 – Large backup file size

backupsql-server-2012

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?

Best Answer

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

Otherwise, the dump is appended to the backup file.

Related Question