|
|
|
|
|
Backup Overview 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 You may ask why I chose 1 hour and 6 hours. 6 hours was just a convenient demamrcation point for differential backkups. This could just as easily have been 2 hours or 4 hours. 1 hour was a convenient demarcation point for transaction logs, this could just as easily have been 15 minutes or 2 hours. You should try to backup the entire server with a full backup each night. In very large environment, this may not be possible. In that case, you should look into some of the alternate backup methods now available in 7.0 such as file and filegroup backups. In any case, you should get an entire server backup at least once during a given period. In general, you should try to keep it to approximately 4 – 8 transaction log backups between differential backups and approximately 2 – 4 differential backups between full backups. This minimizes the number of backup files that need to be restored.It should also be noted that I tend to use a large number of tapes in comparison to many. This is done mainly for simplicity. When backing up, I utilize a 31 tape backup set for the month. If 2 tapes are required for a given day, then 62 tapes are in my rotation. 3 tapes per day takes this to 93 tapes in a rotation. Why do I do this? That way you never have to guess about which tape you will need. If the backups run on the 1st of the month, then they will be on tape 1. If they run on the 15th of the month, then they will be on tape 15. If they isn’t a day in the month corresponding to a tape, that tape is skipped. That way when you need to restore from tape, you only need to know what day of the month it is in order to select the correct tape. Backup Strategy #1 The simplest backup strategy that should be employed combines disk and tape backups. The full, differential, and transnaction log backups are sent to a local disk device. A job then takes these files and archives them to tape. This allows you to have the most recent backups already loaded onto the file system in the event of a restore. It also protects you in the event of a server failure by capturing the backups to tape. The backup to tape can be accomplished with the native NT Backup by using the AT commands to set up a schedule. A much better alternative would be to utilize Enterprise backup tools such as UltraBac, ArcServ, and Backup Exec. These allow you to utilize a central tape drive to store all of your daily backups.
Backup Overview 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
All content on this site, except where noted, represents an original work of Michael R. Hotek and is protected by applicable copyright laws. The SQL Server FAQ is the sole work of Neil Pike. No page, portion of a page, or download may be used for commercial purposes in whole or in part without the express, written permission of the applicable author.