Differential Backup Recovery

A: In this case, createdatabasetestcreatetablet (aint) insertintotest... tselect1 is backed up completely. backupdatabasetesttodiskc: test. bakinsertintotest.. tselect2 is backed up again. Example: test. B

A: In this case, create database test create table t (a int) insert into test .. t select 1 and then perform a full backup database test to disk = 'C: \ test. bak 'insert into test .. t select 2 perform another full backup database test to disk = 'C: \ test. B

A: this is the case.
Create database test
Create table t (a int)
Insert into test.. t select 1
Then perform a complete backup
Backup database test to disk = 'C: \ test. Bak'
Insert into test .. t select 2
Perform another full backup
Backup database test to disk = 'C: \ test. Bak'
Insert into test .. t select 3
Use restore database test from disk = 'C: \ test. bak 'with file = 1
The result is 1, which is correct
Use restore database test from disk = 'C: \ test. Bak' with file = 2
The result is 1,
2 This is also correct
When table t is 1, 2, 3, insert a record with the result of 1, 2, 3, and perform a differential backup.
Backup database test to disk = 'C: \ test. Bak' with differential
Then execute delete from t to delete all records
I want to restore the last differential backup (result 1, 2, 3, 4). How can I implement it with statement change?
---------------------------------------------------------------
The following is a detailed process. The test is successful on my computer:
-- Clear the environment to prevent existing data from affecting test results
Exec master .. xp_mongoshell 'del c: \ text. Bak'
If exists (select * from master .. sysdatabases where name = 'test ')
Drop database test

Go
-- Create a database
Create database test
Go
-- Open the created data
Use test
Go
-- Create a test table
Create table t (a int)
-- Switch back to master database
Use master
Go
-- Insert data 1
Insert into test.. t select 1
Go
-- Then perform a full backup
Backup database test to disk = 'C: \ test. Bak'
Go
-- Insert data 2
Insert into test .. t select 2
Go
-- Perform another full backup
Backup database test to disk = 'C: \ test. Bak'
Go
-- Insert 3, 4
Insert into test .. t select 3
Insert into test .. t select 4
Go
-- Differential backup:
Backup database test to disk = 'C: \ test. Bak' with differential
-- Delete a database
Drop database test
-- Restore database and differential Database Backup
-- Restore full backup
Restore database test from disk = 'C: \ test. bak 'with file = 2, norecovery
-- Restore the differential backup content
Restore database test from disk = 'C: \ test. bak 'with file = 3, recovery
-- Display recovered data
Select * from test.. t
---------------------------------------------------------------
I have already said it clearly. How can this problem be solved?
I have done similar programs some time ago!
It must be noted that the last full backup must be restored when differential backup is restored !! (Remember)
The following two statements must be executed at the same time, that is, in a transaction.
Restore database test from disk = 'C: \ test. bak 'with file = the full backup number closest to the differential backup you want to restore, norecovery
Restore database test from disk = 'C: \ test. bak 'with file = the differential backup number you want to restore, recovery
The specific backup number can be obtained from the following: (you can carefully study several tables, including backupfile, backupset, backmediaset, and backupmediafamily, to find the regular expression)
Select backup_start_date as backup time, position as backup number,
Case type when 'd 'then' full backup 'when' I 'then' differential backup 'end as Backup type
From msdb .. backupset where database_name = 'test'
And media_set_id in
(Select distinct media_set_id from msdb .. backupmediafamily where physical_device_name = 'C: \ test. Bak ')
Order by position
If not, leave a message for me ~
---------------------------------------------------------------
--- Execute the following sequence:
Create database test
Go
Use test
Go
Create table test. t (a int)
Insert test.. t select 1
Backup database test to disk = 'C: \ test. Bak'
Insert test .. t select 2
Backup database test to disk = 'C: \ test. Bak'
Insert test .. t select 3
Insert test .. t select 4
Backup database test to disk = 'C: \ test. Bak' with differential
Delete test.. t
Go
-- Start recovery below:
Restore database test from disk = 'C: \ test. bak 'with file = 2, norecovery -- corresponds to your last full backup
Restore database test from disk = 'C: \ test. bak 'with file = 3 -- corresponds to the differential backup you want to restore
Go
Select * from test