SQL Server database backup test)

-- Create a test
Create Database DB
Go

-- Normal backup
Backup database dB to disk = 'C: \ 1. Bak' with format
Backup log dB to disk = 'C: \ 2. Bak' with format
Backup log dB to disk = 'C: \ 3. Bak' with format
Backup database dB to disk = 'C: \ 4. Bak' with format
Backup database dB to disk = 'C: \ 5. Bak' with format, differential
Backup log dB to disk = 'C: \ 6. Bak' with format
Go

-- The following are common errors used for log backup and differential Backup recovery.

-- 1. Use the wrong log order for recovery
If db_id ('db') is not null DROP DATABASE DB
Restore database DB from disk = 'C: \ 1. Bak' with norecovery
Restore log DB from disk = 'C: \ 3. Bak'
/* -- Receive the message
Server: Message 4305, level 16, status 1, Row 5
Logs in this backup set start with lsn 6000000002800001, which is too late to be applied to the database. Earlier log backups containing lsn 6000000002500001 can be restored.
--*/
Go

-- 2. Apply the log backup to the wrong full backup during recovery
If db_id ('db') is not null DROP DATABASE DB
Restore database DB from disk = 'C: \ 4. Bak' with norecovery
Restore log DB from disk = 'C: \ 2. Bak'
/* -- Receive error message
Server: Message 4326, level 16, status 1, Row 5
Logs in this backup set are terminated at lsn 6000000002800001, which is too early to be applied to the database. New log backups that contain lsn 6000000003000001 can be restored.
--*/
Go

-- 3. Use log backup for Restore database
If db_id ('db') is not null DROP DATABASE DB
Restore database DB from disk = 'C: \ 2. Bak' with norecovery
/* -- Receive error message
Server: Message 3135, level 16, status 2, row 4
The backup set in the 'C: \ 2. Bak' file is created by the backup log and cannot be used for this restoration operation.
--*/
Go

-- 4. Use differential backup for restore log
If db_id ('db') is not null DROP DATABASE DB
Restore database DB from disk = 'C: \ 4. Bak' with norecovery
Restore log DB from disk = 'C: \ 5. Bak'
/* -- Receive error message
Server: Message 3135, level 16, status 2, Row 3
The backup set in the 'C: \ 5. Bak' file is created by backup database with differential and cannot be used for this restoration operation.
--*/
Go

-- 5. Use differential backup in the wrong full backup
If db_id ('db') is not null DROP DATABASE DB
Restore database DB from disk = 'C: \ 1. Bak' with norecovery
Restore database DB from disk = 'C: \ 5. Bak'
/* -- Receive error message
Server: Message 3136, level 16, status 1, Row 3
The backup on the device 'C: \ 5. Bak' cannot be applied to the database 'db '.
--*/
Go

-- 6. directly use Log backup or differential backup for restoration
If db_id ('db') is not null DROP DATABASE DB
Restore database DB from disk = 'C: \ 5. Bak'
Restore log DB from disk = 'C: \ 2. Bak'
/* -- Receive error message
Server: Message 913, level 16, status 8, Row 3
The database with ID 65535 cannot be found. The database may not be activated or is being switched.
--*/
Go

-- 7. If norecovery is not used to restore the full backup, the log backup or differential backup cannot be restored correctly.
If db_id ('db') is not null DROP DATABASE DB
Restore database DB from disk = 'C: \ 1. Bak'
Restore log DB from disk = 'C: \ 2. Bak'
/* -- Receive error message
Server: Message 4306, level 16, status 1, row 4
With norecovery or with standby is not specified in the previous restoration operation. Specify with norecovery or with standby in all the other steps except the last step, and then restart the restoration sequence.
--*/
Go

-- Delete test
If db_id ('db') is not null DROP DATABASE DB