Best Practices

ivy

Is there a best practices for a pretty general setup? We use SQL Server 2005, just bought RedGate SQL Backup. Databases are all FULL for recovery model.

My question relates to order of the backups. Should I be doing a full once a day, transaction every say 30 minutes and once a day after the full backup a transaction log backup with "Remove inactive entries" checked?

I understand the 30 mins is probably relevant to the person application but I'm using it to setup the question.

Any advice is appreciated.

Chris Auckland

Thanks for your post.

The setup that you have described is a pretty typical backup strategy. I would just like to note that you will need to make sure that the full backups and the t-log backups do not occur at the same time, but this should be fairly easy to configure.

With regards to the 'remove inactive entries' transaction log backup option, if you uncheck this option the backup will not truncate the transaction log. Unless you have any particular reason why you would not want to truncate the transaction log, you may as well keep this option selected and always truncate the transaction log. Here is some information on transaction log truncating: http://msdn2.microsoft.com/en-us/library/ms189085.aspx

This would mean that a more simple strategy could be implemented. A daily full backup, followed by a t-log backup every 30 mins.

I hope this is useful.

Let me know if you need any further information.