Security code: Life should be like a candle, from the top burning to the end, has always been bright
1, why do you want to do backup? In daily life, such as: program errors, human errors, computer failures, disk failures, unknown natural disasters (fire, earthquakes), or theft ,
can result in data loss.
2. What is Backup? A backup is a copy of the data that is used to restore and recover data after a system failure, note: file copy ≠ backup
In daily life, for example: websites, schools, libraries, banks and many other places need to use the database, then, as a DBA engineer, the daily must do is data backup.
So, we first learn theoretical knowledge.
Backup type:
Backup Type |
Description |
Full backup |
Backup of the entire database, part of the transaction log, database structure, and file structure, the basis of backup |
Differential backup |
Backup of all changed data after the last full backup, faster and less time required |
Transaction log Backups |
1. After performing at least one full backup to back up the transaction log 2. The transaction log cannot be backed up under the simple recovery model 3. The transaction log cannot be recovered without a database backup that is consistent with it |
Backup and restore policies:
Strategy |
Description |
Full Backup and restore |
Small database, database rarely this table or read-only |
Full + differential backup and restore |
Database changes frequently, want minimum backup time |
Full + transaction log backup and restore |
Database and transaction log backups combined, database changes frequently, full backup time is too long |
Full + diff + transaction log backup and restore |
Reduces the number of transaction log backups required to restore, reducing the time to restore the database |
Recovery mode:
Recovery mode |
Description |
Simple |
Transaction logs cannot be used to restore data for small databases and infrequently changed databases |
Complete |
1. All transactions are recorded in the log 2. Keep all logs until the transaction log is backed up 3. For production database 4, support point-in-time restore |
Large-capacity logs |
Complement of the full recovery model, with minimal logging for bulk operations |
Experimental Demo: See Video data SQL Server backup and recovery
This article is from "A Candle" blog, please be sure to keep this source http://yw666.blog.51cto.com/11977292/1844757
DBA Engineer Daily Process-data backup