The Accidental dba:sql Server Backup

The accidental DBA has recently been re-viewed, and the SQL Server Backup section has been slightly collated for later review.
I. Understanding RTO and RPO
1.1. RTO (Recovery time Objective), downtime, how much downtime is acceptable

--downtime per year (24*365,5-nines means 99.999% up-time)Select  -* -*365*(1-0.99999)--5.25600 minutesSelect  -* -*365*(1-0.9999)--52.5600 minutesSelect  -*365*(1-0.999)--8.760 hours

1.2.RPO(Recovery point Objective), data loss, how much or work it's acceptable to lose
ii. Recovery Models and Backup Types
2.1.Recovery Models (recovery mode)
Full: All modifications to the full log in the database; Truncate the log only if the transaction log backup is performed
Bulk-Logged: Partial modification (such as index rebuild or bulk import) minimizes logging; truncates log only when performing transaction log backups
Simple: Partial modifications, such as index rebuilds or bulk imports, minimize logging, truncate the log when a checkpoint (checkpoint) operation is performed, and cannot make transaction log backups
In summary, if your database uses the full/bulk-logged recovery model, you must periodically perform transaction log backups, or the transaction log will continue to grow.
2.1.Backup types (back-up type)
Complete: Backs up the entire database, which is the basis for other backups. A full backup backs up all data in the data file + partial transaction log records. Transaction logging is used to recover this database when it is complex to transactional consistency. A full backup does not truncate the transaction log, in effect it does not affect the transaction log in any way.
Transaction log: Backs up all transaction log records that have been generated since the last transaction log backup. Transaction log backups cannot be performed until a full backup has been performed, but once a full backup is performed, the transaction log backups and full backups are no longer affected by either party.
diff: Backs up all changed data since the last full backup. Differential backups are cumulative, not incremental. Differential backups are used to speed up recovery, which is essentially equivalent to the net effect of all transaction log backups after the most recent full backup.
Other smaller-grained data backups: Full filegroup Backup, full file backup, differential filegroup backup, differential file backup
The first thing that happens is to perform a transaction log backup (tail-log backup) that captures all the transaction logs since the last scheduled transaction log backup. Then follow the full backup + differential backup + transaction log backup + tail-log backup in the order of recovery.
third, planning a Recovery strategy
You have to be clear about how quickly you need to recover a database to a point, and then use that information to set up backups and recover correctly when a disaster strikes.
In terms of data loss, tolerable data loss defines how often transaction log backups are performed. If you can tolerate 15 minutes of data loss, you must perform a transaction log backup at least every 15 minutes. If 0 tolerates data loss, it is dangerous to rely on transaction log backups because a disaster can corrupt data and log files so that tail-log backups cannot be made. In this case, you should choose a synchronization technique such as data mirroring, AlwaysOn, and so on.
In terms of downtime, tolerable downtime defines how quickly the entire recovery sequence will be completed, from the most recent full backup to the last transaction log backup recovery. This means that you will have to test the recovery to ensure that in the worst case it can be resumed within the downtime.
3.1. Recovery Strategy
1. Define the database downtime and data loss requirements
2. Calculate the recovery required to meet step 1
3. Calculate the backup required to meet step 2
4. Test the recovery sequence to ensure that it meets the conditions of step 1
5. If the recovery sequence does not meet step 1, return to step 3 (or postpone the outage requirement, or provide a highly available solution that allows rapid failover to a redundant copy of the database)
iv. Essential BACKUP Options
COMPRESSION: Compress backups, reduce backup files, and speed up backup recovery
Copy_only: For a periodic backup plan, you want to get a single backup file, using this option does not affect the differential backup base/transaction log backup Chain
DESCRIPTION and file Names: Backup description and provide a clear file name
CHECKSUM: Check checksum before writing to media
STATS: Print Execution progress
V, Backup testing for Validation
If you have a backup with the checksum option, you should at least do something similar:

 restore   verifyonly  from  disk  =  n '  

It is best to actually perform the restore operation, use the checksum option, and then run the consistency check (DBCC CHECKDB) to make sure that the backup you will use is valid.
If you find that your backup is damaged, you can make a new backup or use a "relatively old" backup, which you keep.
Vi. Backup Storage and Retention
6.1. Backup storage
The worst place to store backups is the I/O subsystem, which is the same as the database itself.
A common backup storage strategy: first back up to local disk storage, back up to a network share, or back up local storage and then copy to a network share, then copy the backup to the online archiving system and create an offline backup.
6.2. Backup retention Period
If you are unable to use the latest full backup, then it is best to use the full backup before it, and then combine the other differences + transaction log backups so that you can recover the data.
If the backup policy is full backup/week + differential backup/day + transaction log backup/30 minutes, I will try to keep the online backup for one months, offline for three months.
Vii. Summary
1. What are downtime (RTO) and data loss (RPO) requirements and why they are important to your database
2, three recovery models, how they affect log and log backups; Three primary types of backup (full, differential, transaction log)
3. How to design a recovery strategy that takes into account RPO and RTO requirements and create a backup strategy
4. Common Backup Options
5. Verify backup files to ensure that they are available when you need them
6. Store multiple copies of the backup file (local/remote, online/offline); Backup retention period, which can be recovered using "relatively old" backups during disaster recovery

The Accidental dba:sql Server Backup