Parsing SQL Server database backup and restoration policies

A. Causes of data loss:
Program error
Human error
Computer error
Disk failed
Disasters (such as fire, earthquake) and theft
B. What is backup?
Backup creates a copy of data.
Used to restore and restore data after system failure
File copy is not equal to backup
C. Backup type
Full Backup: the database at the time of backup completion provides a benchmark for any other backup.
Differential backup: you have performed a full backup in advance to back up all the changed data after the last full backup.
Transaction log backup: a full backup has been performed in advance, and the backup of confirmed transaction logs is truncated from the previous transaction log backup or full backup to the end of the current transaction log.
File or file group backup
D. Backup media supported by SQL Server
Disk
Tape
Network shared files
E. Backup device
Physical Device: c: \ backup \ full. bak
Logical backup: stored in the sysdevices table of the master database of the system database. Use the command sp_addumpdevice to create a logical device.
F. Backup policy
Full backup and restoration: small databases with few changes or read-only
Full Backup + differential backup and restoration: frequent database changes and minimum backup time required
Full Backup + log backup and restoration: the database is often changed, and the full backup takes too long
Full Backup + differential backup + log backup and restoration: This reduces the number of transaction log backups to be restored and shortens the database recovery time.
Full backup and restoration:

The code is as follows:	Copy code
Backup database <database_name> to disk = 'd: \ backup \ database_name.bak 'with init, format; Restore database <database_name> from disk = 'd: \ backup \ database_name.bak 'with recovery;

Full Backup + differential backup and restoration:

The code is as follows:	Copy code
Backup database <database_name> to disk = 'd: \ backup \ database_name.bak 'with init, format; Backup database <database_name> to disk = 'd: \ backup \ database_name.bak 'with differential; Restore database <database_name> from disk = 'd: \ backup \ database_name.bak 'with file = 1, norecovery; Restore database <database_name> from disk = 'd: \ backup \ database_name.bak 'with file = 2, recovery;

PS: with file = <n> indicates that the backup file contains both full backup and differential backup. The full database backup to be restored is the 1st backup sets on the device, differential backup is the 2nd backup sets on the device.
For different files, you do not need to specify with file = <n>
For multiple differential backups, you only need to use the last differential backup at the time point you need to restore.
Full Backup + transaction log backup and restoration

The code is as follows:	Copy code
Backup database <database_name> to disk = 'd: \ backup \ database_name.bak 'with init, format; Backup log <database_name> to disk = 'd: \ backup \ database_name.trn '; Restore database <database_name> from disk = 'd: \ backup \ database_name.bak 'with norecovery; Restore log <database_name> from disk = 'd: \ backup \ database_name.trn 'with recovery;

PS: multiple log backups need to be restored multiple times
Full Backup + differential backup + transaction log backup and restoration

The code is as follows:

Copy code

Backup database <database_name> to disk = 'd: \ backup \ database_name.bak 'with init, format; Backup database <database_name> to disk = 'd: \ backup \ database_name.bak 'with differential; Backup log <database_name> to disk = 'd: \ backup \ database_name.trn '; Restore database <database_name> from disk = 'd: \ backup \ database_name.bak 'with norecovery; Restore database <database_name> from disk = 'd: \ backup \ database_name.bak 'with file = 2, norecovery; Restore log <database_name> from disk = 'd: \ backup \ database_name.trn 'with recovery;

Or use the following to restore to a specific time point:

The code is as follows:	Copy code
Restore log <database_name> from disk = 'd: \ backup \ database_name.trn 'with recovery, stopat = '2017-09-09 13:26:00 & prime ;;