| econdary page cannot use any JSP constructs that affect the main page as a whole. Use of jsp:include is discussed in the second subsection. � The location of backup storage. Whether it is on a tape or disk, the media the backups are stored on take up physical room. Do you store the media on site? off site? If your corporate disaster recovery strategy states that you must be able to go back a week, it might make sense to keep a week (or even two) on site, but anything older than that should be stored in a secure, climate-controlled off-site facility. Also, you might be at risk if your datacenter is damaged and it not only housed your systems, but your complete corporate archive of backup tapes as well. Remember that backup tapes take up physical storage space and have a cost associated with them, so you might not be able to keep every backup since the inception of your company. Many companies implement an archival and rotation schedule of somewhere between 30 days and 90 days, unless they are regulated by some external auditing agency or specific industry rules (for example, a financial company) and are mandated to retain large amounts of archived backups. � Off-site location access. If you store your backups at an off-site location, you need to have physical access to the location at all hours, so choose your storage facility wisely. On the CD Use the file SQL_DB_Backup_Info.xls to record your backup strategy. It has two main tabs that you can copy and modify. One is for an entire instance of SQL Server so you can record the databases, and one is per application. This strategy allows you to have two views to understand the scope of your backup strategy better. Devising a Backup Strategy to Create an Optimal Recovery Strategy The most effective backup approach combines two strategies: one strategy is based on full database backups (the full database-based backup strategy), and the other is based on file/filegroup backups (the file-based backup strategy). Both strategies can use differential backups, but all strategies involve the use of transaction log backups. The Full Database-Based Backup Strategy This strategy is the most common strategy. It is generally based on backup types that have been available since the introduction of SQL Server, yet if the environment is right it might also utilize one of the newer backup types to improve recovery time. This strategy is easier to administer than the file-based backup strategy and offers up-to-the-minute recovery and point-in-time recovery. You can change between the Full and Bulk-Logged recovery models without breaking the continuity of the log. Remember to also review the recommended practices for batch processing, because using the Bulk-Logged recovery model will significantly reduce the work-loss exposure created during the changes in recovery models. The typical strategy consists of automated backups of these types: � Full database backups � Differential database backups � Transaction log backups Their frequency is dictated by your database but usually follows something like this: � Full database backups weekly (although less frequent full backups are an option, as these might be quite large and expensive to manage). � Differential database backups nightly (except on the night of the full database backup). � Transaction log backups-consistent and automated, as well as two special cases. The frequency dictates the potential work-loss exposure in terms of minutes and the transaction log size. The smaller the interval, the smaller the log will be, which is, of course, dependent on database activity. � Automated Hourly, every 30 minutes, every 5 minutes, every 1 minute, and so on. � Special Case 1 (Proactive) When the transaction log "percent log used" reaches a higher than normal level, back it up proactively before it fills up. � Special Case 2 (Reactive) When the transaction log fills up, back it up. Consider the following example using full and transaction log backups, as well as differentials, because performing full database backups weekly leads to a large number of logs being created hourly. In a week, if you performed a transaction log backup every hour, you would have nearly 170 transaction log backups (24 ? 7 = 168, but because you perform one full backup, no transaction logs are generated during the process). Restoring all of these transaction logs from transaction log backups takes time and is cumbersome. You want a streamlined process, not one in which you need to write close to 180 Transact-SQL statements. So although it takes significantly less time than the original modification took to apply all of the operations in the transaction logs, it could |
