[SQL Server] No worries-backup and recovery

In SQL Server, you can use database backup and restoration tools to create a copy of the database and place the copy to a safe place. When the server crashes or data is damaged, this copy can be used to restore the database. This is what we will talk about in this article: backup and recovery.

(1) complete backup and recovery

Make copies of all the content in the database, which takes the most time and space during the backup process, and should not be performed frequently

You only need to restore the last full-database backup.

Backup: backup database name to backup device name with [name = 'backup name'] [init/noinit]

backup database MagDB to MagDb_1 with init

Recovery: Restore database name from backup device name with [norecovery/recovery]

restore database MagDbfrom MagDb_1with norecovery

(2) differential (incremental) backup and recovery

Only the data modified after the last full database backup is backed up, and the backup time and space are small.

Restore the last full backup Before restoring the last differential backup.

Backup: backup database name to backup device name with differential [name = 'backup name']

backup database MagDb to MagDb_1 defferential

Recovery: Restore database name from backup device name with [norecovery/recovery]

restore database MagDbfrom MagDb_1with file =2,recovery

(3) Transaction Log backup and recovery

Only the transaction log records after the last log backup are backed up, and the backup takes less time and space.

During recovery, you can specify to recover to a transaction; you can restore it to a transaction before a destructive operation is executed, which is not possible for full-database backup and differential backup, however, when using log backup for recovery, you need to re-execute the modification command in the log record to restore the data in the database. Therefore, the restoration usually takes a long time. First, the last full database backup is restored, restore the last differential backup, and restore all transaction log backups after the last differential backup in sequence

Backup: backup log database name to backup device name with init/noinit

backup log DocDb to disk='c:\databak\DocDb_1.bat'

Recovery: Restore log database name from backup device name with [norecovery/recovery]

restore log DocDbfrom disk='c:\databak\DocDb1.bat'

(4) file and file group backup and recovery

To back up a database file or database file group, it must be combined with transaction logs to make sense.

Transaction logs are used to restore all data files to the same time point.

Backup: backup database name file = 'logical Name Of The file' (filegroup) to backup device name with init/noinit

backup database DocDb file='DocDb_Data'to disk='c:\databak\Docfile1.dat'

Restore: Restore database name file = 'logical Name Of The file' (filegroup) from backup device name

restore database DocDbfile="DocDb_Data"from disk="c:\databak\Docfile1.dat"

It is easy to back up and restore your data.