Cơ sở dữ liệu - Chapter 2: Disaster recovery

Chapter 2Disaster Recovery3/29/20201AgendaRecovery techniqueDisaster recovery planning3/29/20202Overview about database backup and recoveryData 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.3/29/20203Overview about database backup and recoveryTypes 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.3/29/20204Overview about database backup and recoveryThe 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.3/29/20205Backup & restore strategyThe backup strategy defines:The type and frequency of backupsThe nature and speed of the hardware that is required for themHow backups are to be testedWhere and how backup media is to be stored (including security considerations) 3/29/20206Backup & restore strategyThe restore strategy defines:Who is responsible for performing restores How restores should be performed to meet the goals for availability of the databasefor minimizing data lossDesigning an effective backup and restore strategy requires careful planning, implementation, and testing 3/29/20207Backup methodDatabase Engine provides four different backup methods:Full database backupDiferential backupTransaction log backupFile (or filegroup) backup3/29/20208Backup methodFull 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.3/29/20209Backup methodDifferential BackupCreate 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.3/29/202010Backup methodTransaction 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.3/29/202011Backup methodFile 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 backup procedure and still be able to restore individual files (or filegroups) from the database backup3/29/202012Recovery modelDesigned to control transaction log maintenanceEvery 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:SimpleFullBulk LoggedHowever, only Simple and Full recovery models are meant for regular use. Bulk Logged,is designed to be an adjunct to the Full recovery model.3/29/202013Recovery modelSimple Recovery modelMinimize administrative overhead for the transaction log, because the transaction log is not backed upData is recoverable only to the most recent backup of the lost data3/29/202014Recovery modelFull Recovery modelAll activities that affect the database are logged in the transaction log. The advantage of this full logging is that every transaction can be recovered in the event of a failure.3/29/202015Recovery modelBulk log recovery modelLog most bulk operations. For certain large-scale bulk operations such as bulk import or index creation, switching temporarily to the bulk-logged recovery model increases performance and reduces log space consumption.3/29/202016Backup TypesThe scope of a backup of data (a data backup) can be a whole database, a partial database, or a set of files or filegroups.SQL Server supports 3 backup types:Full backupDifferential backupTransaction log backup3/29/202017Backup TypesFull backup: Contain all the data in a specific database or set of filegroups or files, and also enough log to allow for recovering that data.Differential backup:based on the latest full backup of the data.Contain only the data that has changed since the differential base.Transaction log backup:In Full or Bulk-Logged recovery models3/29/202018