SQL Server 2005 - Backup Strategies

Over the past 5 years we have tried a few different backup strategies for our SQL Server data. This includes SQL Server 2000 onwards.

I thought it would be a good excercise to discuss our current backup strategy and how it evolved into its current state.

Current Backup Setup
There are 3 sections to our current backup strategy.

1. Full Daily Backup
2. Transactional Log Backup
3. CleanUp old backups

Full Daily Backup
A full daily backup of all databases is done in the evening. This is done by using the selection 'All user databases' in the backup job setup.

Transactional Log Backup
The transaction logs for our mission critical database are backed up every 15 minutes. The rest of the user databases have their transaction logs backed up every hour during working hours.

CleanUp old backups
Cleanup is done before evening backups and it varies depending on the database. All databases have a maximum of 5 days full backups. Most databases are have their clean up to be less than 5 days.

Transaction logs are kept for a maximum of 3 days with most databases having between 1-2 days transaction logs.

Looking at our strategy you may have some questions in you mind. I will try to provide a rational for our backup strategy.

We are using one maintenance plan to backup all user databases because using this method you can be sure that all current databases will at least have a full daily backup. Occasionally we had developers or third parties install applications with databases onto our database server without DBA's knowing. This would mean that DBA's were unaware of new databases that required backing up until they checked the server or were told about it. The occurrence of this issue have been much reduced in recent times but it does still happen.

The cleanup routine should ideally occur after a successful backup however due to space restrictions on the backup drive we have had to do cleanup before doing the evening backups.

We have a standard Mirror, Mirror, RAID-5 setup on our server. This means the OS is on 2 mirrored drives, Logs are stored on 2 mirrored drives and the MDF database files are stored on a RAID-5. In order to protect from multiple drive failure we has set our backups so that all Log backups go to the database RAID-5 partition and all full database backups go to the Logs mirror partition. This backup configuration gives us fully backed up databases in the event that a whole set of disks fails. For example if the RAID-5 array fails that we will have the logs and the database backups on the Logs drive. If the Logs disks fail then we would have the live database files and the log backups from the RAID-5 drives.

This setup isn't perfect but I believe that it is a decent real world solution to backing up our data. We are lucky that we have a large backup window were all databases have minimal usage so that we can perform backups in the evening.

Please let me know if you have any comments about my backup strategy.