Bài giảng Cơ sở dữ liệu nâng cao - Chapter 2: Disaster recovery

Bài giảng Cơ sở dữ liệu nâng cao - Chapter 2: Disaster recovery. Chương này trình bày một số nội dung sau: Overview about database backup and recovery, backup & restore strategy, disaster recovery planning,... Mời các bạn cùng tham khảo.
Nội dung trích xuất từ tài liệu:
Bài giảng Cơ sở dữ liệu nâng cao - Chapter 2: Disaster recovery4/5/17 1 Chapter 2 Disaster Recovery4/5/17 2 Agenda• Recovery technique• Disaster recovery planning4/5/17 3 Overview about database backup and recovery• Data is a critical asset for an organization to maintain information about its customers, inventory, purchases, financials, and products.• A variety of failures can bring down your database. Anything from a user error to a natural disaster could take your database offline. Your backup and recovery plan needs to account for the possibility of these failures and more.4/5/17 4 Overview about database backup and recovery• Types of failure: • Media failure. • User errors, for example, dropping a table by mistake. • Hardware failures, for example, a damaged disk drive or permanent loss of a server. • Natural disasters.4/5/17 5 Overview about database backup and recovery• The purpose of creating SQL Server backups is to enable you to recover a damaged database.• A well-designed backup and restore strategy maximizes data availability and minimizes data loss, while considering your particular business requirements.4/5/17 6 Backup & restore strategy• The backup strategy defines: • The type and frequency of backups • The nature and speed of the hardware that is required for them • How backups are to be tested • Where and how backup media is to be stored (including security considerations)4/5/17 7 Backup & restore strategy• The restore strategy defines: • Who is responsible for performing restores • How restores should be performed to meet the goals • for availability of the database • for minimizing data loss• Designing an effective backup and restore strategy requires careful planning, implementation, and testing4/5/17 8 Backup method• Database Engine provides four different backup methods: • Full database backup • Diferential backup • Transaction log backup • File (or filegroup) backup4/5/17 9 Backup method• Full database backup: • Capture the state of the database at the time the backup is started. • During the full database backup, the system copies the data, the schema of all tables of the database and the corresponding file structures. • Even all uncommitted transactions in the transaction log are written to the backup media.4/5/17 10 Backup method• Differential Backup • Create a copy of only the parts of the database that have changed since the last full database backup. • The advantage of a differential backup is speed. It minimizes the time required to back up a database.4/5/17 11 Backup method• Transaction Log Backup: • Consider only the changes recorded in the log. This form of backup is therefore not based on physical parts (pages) of the database, but rather on logical operations - that is, changes executed using the DML statements INSERT, UPDATE, and DELETE. Again, because the amount of data is smaller, this process can be performed significantly quicker than a full database backup and quicker than a differential backup.4/5/17 12 Backup method• File or Filegroup Backup: • Back up specific database files (or filegroups) instead of the entire database. • Individual files (or filegroups) can be restored from a database backup, allowing recovery from a failure that affects only a small subset of the database files. • You can use either a database backup or a filegroup backup to restore individual files or filegroups. This means that you can use database and transaction log backups as your backup4/5/17 13 Recovery model• Designed to control transaction log maintenance• Every recovery model lets you back up a whole or partial SQL Server database or individual files or filegroups of the database. Table-level backups cannot be created.• SQL Server has three possible recovery models: • Simple • Full • Bulk Logged• However, only Simple and Full recovery models are meant for regular use. Bulk Logged,is designed to be an adjunct to the Full recovery model.4/5/17 14 Recovery model• Simple Recovery model • Minimize administrative overhead for the transaction log, because the transaction log is no ...