May 15, 2008 at 3:08 pm
We have a SQL2K box with several production databases. The sizes vary, but the largest is around 10gb, nothing extremely large. We are doing full backups in the evening (when there is light use of the database) on all the databases. Differential backups are performed every 2 hours during the normal course of business from 8 am to 4 pm. Log backups are done every 30 minutes during the day as well. Overall, this seems to fit with our requirements for recovery.
Where my main question is, we have been backing up to disk directly on a separate server (where all backups throughout the company are pointed at) which is then picked up by the tape. There was an issue with the server that collects all the backups this morning which caused all of the SQL Server backups to fail until the server was restored. This opened up a hole in our process. Our plan is to back up to the local server then copy that file to the other server where the tape is pointed to by adding a step to the job that would issue the copy command.
The IO system on the SQL box is 3 drives in a RAID 5 and 2 in a RAID 1. The databases are on the RAID 5 and the logs along with the OS are on the RAID 1 (different partitions, not sure if that matters).
The plan is to have the backups go to a partition on the RAID 1 drive set. Then, copy the backup files (as part of the job) to the other server that the tape is pointed to. Are we missing anything here? Are there any potential problems that are being overlooked? I welcome any input.
May 19, 2008 at 3:22 pm
I cannot see any potential issue but one point why you need both transactional backups and differential backups.
May 19, 2008 at 3:49 pm
I think you kind of stumped me. I thought that was a best practice. If there was a need to restore, use the last full, then the last differential, then any log backups up to the point of failure. Is that not how it should be done? I would assume you can use just the last full then apply the log backups until the point of failure as well. Is that what you recommend?
This is what BOL says:
DIFFERENTIAL
Specifies the database or file backup should consist only of the portions of the database or file changed since the last full backup. A differential backup usually takes up less space than a full backup. Use this option so that all individual log backups since the last full backup do not need to be applied.
This is what we based our strategy off of. Any input you have is appreciated.
May 20, 2008 at 12:19 am
dbaInTraining (5/19/2008)
I think you kind of stumped me. I thought that was a best practice. If there was a need to restore, use the last full, then the last differential, then any log backups up to the point of failure.
Yup. That's spot on.
The diffs look a little more frequent than maybe necessary. Is there a massive amount of activity on the server? Do you have very short windows to restore the DB if necessary? Do you have policies that specify the maximum number of backup files that should be needed for a restore?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 20, 2008 at 12:00 pm
On the diffs, it is a fairly busy database (at least two of them are), but we will consider the necessity on having them so frequently.That does bring up another question, is there an need to append the diff backups to the backup file throughout the day or just keep the latest one for that day? I know that if you need point in time that happens to be prior to the last diff you can work with the last full and then apply the logs. I am kind of getting the idea that diff are nice as a shortcut (time wise in a restore situation) but not all that necessary. Maybe I am a little paranoid :unsure:, but what if something is wrong with the log backups? Having a sequence of diffs and logs, is that overkill? Just to clarify for the record, there is no official policy from management as to a specified window of time to restore, max downtime, max files to restore, etc. Just to have an effective plan with the lowest risk to the company. So, it is kind of up to me to have a solid plan in place. That is why I am asking.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply