Forum Discussion
Problem with differential backups, after a problem with a full backup
I have a database in Microsoft SQL server 2008 R2, in which I have configured the maintenance plan for backups (a full back on Tuesdays, Thursdays and Saturdays at 00, three transactional logs at 4, 5 and 6 am, and then at 7 am a differential, and so on, finishing with a differential backup at 11 PM). At the beginning of July, there was a problem with a full backup that was not done, because of lack of space (this problem was July 5th), then the differentials began to increase in size, up to 10 GB, when the full was done, the differentials decreased in size to 1 GB (before this, the differentials had a maximum size of 800 MB), the problem is that every day it increases in size, until today, when each differential weighs 6 GB, the full backup's size is about 96 GB because the database is too old. and I've seen some strange behavior, when the plan gets to the differentials, at 7:46 (for example), the file is finished creating, and it weighs 500mb, but then, 1 minute later, it's like the differential is overwritten, and there it increases in size up to 3GB. What could be causing the error? Maybe it's due to something with the TRUNCATE of the full backup? How can I solve this?
I've already tried doing a new manual full backup (on a day that it's not done, at 00 am), and at the moment that it finished being done, a new manual differential, but that didn't solve it. The queries that I run: FULL BACKUP:
BACKUP DATABASE [xxx] TO DISK = N'\xxx\SQLServerDatabases\Backups\full_reset.bak' WITH INIT,
NAME = N'Full_Reset', SKIP,
STATS = 10;
DIFFERENTIAL BACKUP:
BACKUP DATABASE [xxx] TO DISK = N'\xxx\SQLServerDatabases\Backups\diff_reset.dif' WITH DIFFERENTIAL,
INIT,
NAME = N'Diff_Reset', SKIP, STATS = 10;