Database backup and recovery

 

Database backup and recovery are basic requirements for database operations. Learn more about database backup and recovery.

The range of data backup can be a complete database, a part of the database, or a group of files or file groups. Generally, SQL Server supports full backup and differential backup.

Full backup: includes all the data in a specific database (or a group of specific file groups or files) and enough logs that can recover the data.

Differential backup: the latest and complete data-based backup. This is called the benchmark of the difference or the benchmark of the difference. The differential benchmark is a complete backup of read/write data. Differential backup only includes data that has changed since the differential benchmark is established. Generally, the differential backup performed in a short period of time after the baseline backup is established is smaller than the complete backup benchmark, and the Creation speed is faster. Therefore, differential backup can accelerate frequent backup and reduce the risk of data loss. Generally, a differential benchmark is used by several successive differential backups. During restoration, the full backup is restored first, and then the latest differential backup is restored.

1. Create a full database backup
Full database backup should be created in a single operation and is generally scheduled to be performed at a specified interval.

The BACKUP syntax required for creating a full database BACKUP is:

Backup database database_name TO backup_device

1. Back up a database to a disk

'C: \ AdventureWorks. Bak'
USE AdventureWorks
GO
Backup database AdventureWorks
To disk = 'C: \ MSSQL \ BACKUP \ AdventureWorks. Bak'
With format,
NAME = 'full Backup of adventureworks'
GO
-- Optionally, create a logical backup device,
-- AdventureWorks_Backup, for this backup file.
USE master
EXEC sp_addumpdevice 'disk', 'adventureworks _ backup ',
'C: \ MSSQL \ BACKUP \ AdventureWorks. Bak'

2. Back up the database to tape

USE MyAdvWorks
GO
Backup database MyAdvWorks
To tape = '\. \ tape0'
With format,
NAME = 'full Backup of myadvworks'
GO
-- Optionally, create a logical backup device,
-- AdventureWorks_Backup, for this backup tape.
USE master
GO
EXEC sp_addumpdevice 'twap', 'myadvworks _ Bak', '\. \ tape0'

2. Create a differential Database Backup (back up the database before creating a differential database backup; otherwise, you cannot create a differential database backup)

-- Create a full database backup first.
Backup database MyAdvWorks
TO MyAdvWorks_1
WITH INIT
GO
-- Time elapses.
-- Create a differential database backup, appending the backup
-- To the backup device containing the full database backup.
Backup database MyAdvWorks
TO MyAdvWorks_1
WITH DIFFERENTIAL
GO

3. Restore the database and restore the differential backup database

1) restore the complete database

USE master
GO
Restore database AdventureWorks
From tape = '\. \ tape0' ------- Database Backup
GO

2) restore the differential backup database

(1) execute the restore database statement and specify the NORECOVERY clause to RESTORE the full DATABASE backup performed before the differential DATABASE Backup (see the full backup)

(2) execute the restore database statement to RESTORE the differential DATABASE Backup

Restore database MyAdvWorks
FROM MyAdvWorks_1
WITH NORECOVERY
GO

Restore database MyAdvWorks
FROM MyAdvWorks_1
With file = 2,
RECOVERY
GO