Part III in Visual C#.NET

Generator EAN13 in Visual C#.NET Part III

There are several new features in SQL Server 2005 that provide enhanced backup and restore capabilities. These features provide more options, flexibility, and reliability for your backup process. The new or enhanced backup features that are discussed throughout this chapter include backing up to mirrored media, partial backups, copyonly backups, and full-text catalog backups.

You may wonder whether backups are always necessary, particularly if your system is designed for high availability with redundant components, such as with disks drives protected by RAID fault tolerance (see 4, I/O Subsystem Planning and RAID Configuration ), servers that are clustered for failover (with Microsoft Cluster Services and SQL Server 2005 Failover Clustering), and a fully redundant SAN storage system (see 7, Choosing a Storage System for Microsoft SQL Server 2005 ). These highavailability methods provide protection or failover capabilities only for certain software and hardware component failures, such as a power failure for one clustered server node or a single disk drive failure. However, they do not provide protection from all possible causes of data loss. Problems such as a user accidentally deleting data from the database, unexpected data corruption from a software or hardware failure, or an entire disk cabinet failure are not protected by typical high-availability or fault-tolerance methods. In addition, a disaster such as flooding, fire, or hurricane can also destroy your entire datacenter, including your data. In all of these cases, backups are necessary for recovering your data. In a disaster situation, the backups must also be stored at another site or they will also be destroyed. See 25, Disaster Recovery Solutions, for more information on this topic. Note

Even if you have a fully redundant and highly available hardware system, you absolutely need to perform database backups of your critical business data. There is no substitute for database backups.

Backups are also useful in other cases not related to system failures and data loss. You could use a backup to set up database mirroring or to restore a database to a development or test system. You may also archive backup files over months or years so they are accessible if they are needed for an audit.

14

To to help you better understand when and why backups are required, we will discuss specific types and examples of failures from which data can be restored only if there is a backup of the database. One assumption made in this section is that there is no disaster recovery site, a secondary datacenter to which all data is replicated so that it could take over in the event of failure at the primary datacenter. You might conclude that you can always use the disaster recovery site as a backup site if you keep it up-to-date, but you should still always create backups of your data in case even the disaster recovery systems incur any of the failures we discuss below as well.

As we ve mentioned, you can protect your data from many hardware failures using highavailability solutions such as disk RAID and server clustering, but this does not cover all hardware failures. The following are possible hardware failures that require a database backup in order to restore the lost data:

Disk failure with no RAID If you do not have RAID fault tolerance configured or if you are using RAID-0 for the disk drives where the SQL data and/or log files reside, then the failure of any disk drive causes data loss. In this case, the failed disk must be replaced and configured into the system, and the database must be restored from backups. Catastrophic event If a disaster occurs at the datacenter and the system hardware is damaged or destroyed, all data can be lost. In this case, an entirely new system would have to be built, and the data would have to be restored onto it from backups. Multiple component failures If more than one component fails at a time, such as multiple disks of a RAID array or the entire disk cabinet resulting in the array being unable to recover data by simply replacing disks, then a backup is needed to restore the data. Security breach There is the possibility that someone could purposely damage a system as an act of sabotage and destroy data.

There can be other unexpected scenarios that can cause data loss, such as data corruption on a disk caused by disk subsystem failures. All of these can be recovered only by restoring a database backup.