A backup and recovery plan is necessary for the databases that the ABC Company uses for its business needs. In the event of a natural disaster, human error, hardware failure, or any other corruption or loss of data, backup and recovery measures will mean the difference between an inconvenience and a catastrophic loss of company data. The following report addresses various options for backup and recovery, and proposes what measures ABC Inc should adopt for its SQL Server 2012 servers.
SQL Server 2012 supports three scopes of backup and an additional special purpose backup type. The first of the three backup types is whole database which as its name implies, backs up the entire database. A partial database backup backs up read/write filegroups “and, possibly, one or more read-only files or filegroups” according to Microsoft (n.d.). File or filegroup backups are conducted on singe file or filegroups. Finally, a copy-only backup is an out of schedule backup that is initiated for a special purpose, and does not affect the normal cycle of backups.
When a backup is conducted, it can be a full backup or a differential backup. A full backup will copy the entire database, while a differential backup will backup only the changes that have been made since the last backup was conducted. Whole, partial, and file/filegroup backups all can be full or differential backups. A copy-only backup can only be a full backup because it does not change the transaction log, and therefore is outside of the scope of routine backups. According to Microsoft, “A copy-only backup cannot serve as a differential base or differential backup and does not affect the differential base” (n.d.).
An online backup is the process of backing up data while the system is active… or online. SQL Server 2012 supports online backups which will be used in the company’s backup plan. Whole, full system backups will be processed daily in the late evenings, when database usage is the lightest. On a continual basis, differential transaction log backups will happen every 15 minutes, which will keep sequential database changes backed up in between full backups. All database backups will use a mirrored media set that saves backup data to an onsite backup server as well as an identical offsite server at one of the company’s satellite offices. The offsite backup provides reasonable assurance that a natural...