Backup Strategy

Question

Post reply

Backup Strategy

David-155102

SSCarpal Tunnel

Points: 4361
More actions
September 17, 2007 at 2:48 am

#177479

Hi,
Looking for advice here. I have backup jobs that are failing left right and centre so I wanted to see if the backup strategy I have adopted is the most efficient.
Firstly, I have jobs that run differential backups every two hours for each user database - these backups get written to a backup device for the particular database.
Secondly, I have transaction log backups that run every hour for each user database - these backups get written to the same backup device as above.
Lastly, I have full backups that run overnight every night for each user database - again these backups get written to the same backup device above but will overwrite any existing backup.
The log and differential backups do not overwrite existing backups, they are set to append.
I also have an alert that runs an appropriate backup whenever any of the logs or files reach a certain percentage.
The problem I am experiencing is that in a very short space of time I have run out of disk space - the server has 50Gb capacity. When the databases were running on SQL Server 2000 (using the Simple recovery method) the backup for the main db was around 16Gb however now that we are on SQL Server 2005 (using the Full recovery method) the backup for the main db has shot up to nearly 30Gb.
Any help will be greatly appreciated.
Regards

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

ChiragNS One Orange Chip Points: 26137 More actions · Answer 1

Hi

Has your log file has grown to a large size.

"Keep Trying"

Ian Yates SSCoach Points: 19738 More actions · Answer 2

Your log/diff backups are set to append... Does this mean that Tuesday's log/diff backups are being appended on to the end of Monday's log/diff backups, or are the Monday log/diff backups being deleted (since they're no use without their prior full backup anyway!)

David-155102 SSCarpal Tunnel Points: 4361 More actions · Answer 3

I've set all backups for a single user db to backup to a device i.e. log/diff and full all go to the same device. My idea was that when the full backup ran overnight it would over write all of the log/diff and full backups already on the device. The full backup is set to Overwrite. Have I misunderstood how this works?