[Distinct] SQL File Group Backup and restoration (2)

From: http://blog.sina.com.cn/s/blog_59e866610100dnku.html

The last time I backed up and restored SQL file groups, I always felt that I was missing something. The end log is backed up to restore the database to the point where the fault occurred. However, if the fault does not occur, but due to misoperations, you want to restore it to the previous point. Of course, you also need to back up the tail log first. Otherwise, the restoration will fail, however, do not restore the tail log when restoring it.

I created a test database and two file groups, primary and FG1. FG1 is the default file group,

(1) first create a table grade in FG1, and then make a complete backup (base backup) for both file groups ).

(2) Insert some data into Table grade and back up transaction logs.

(3) another table mark is created in FG1.

(4) Differential backup is performed for both file groups.

(5) Insert 10 records into the table mark.

(6) Back up transaction logs.

(7) Insert 10 more records into the table.

(8) Back up transaction logs.

The backup information is as follows:

Now I want to restore the database to the status at step (6.

Use master
Go
Restore database Test
Filegroup = 'primary'
From myback1, myback2
With file = 1, norecovery
Go
Restore database Test
Filegroup = 'fg1'
From myback1, myback2
With file = 2, norecovery
Go
Restore database Test
Filegroup = 'primary'
From myback1, myback2
With file = 4, norecovery
Go
Restore database Test
Filegroup = 'fg1'
From myback1, myback2
With file = 5, norecovery
Go
Restore Log Test
From myback1, myback2
With file = 6, recovery
Go

This step is successfully tested.

Note: No matter which file group you want to back up, the primary file group primary must be backed up first. Otherwise, an error will be reported when backing up the differential backup of other file groups, the system prompts that the base backup does not exist. Because the primary file group contains the primary data file, it is used to store the database startup information and metadata, including system tables and objects.

In the above test, there was another transaction log backup between the full file group backup and differential backup. At the time of restoration, the transaction log was not restored. However, if the differential backup is not performed on the main file group after the transaction log backup, but the differential backup is performed on the file group FG1, the log must be restored during restoration, it is restored after the full file group is backed up and restored (in order). Otherwise, it will prompt you that this log is too late when you restore the transaction log, error message that cannot be applied to the database.

The restoration result is as follows:

Use master
Go
Restore database Test
Filegroup = 'primary'
From myback1, myback2
With file = 1, norecovery
Go
Restore database Test
Filegroup = 'fg1'
From myback1, myback2
With file = 2, norecovery
Go
Restore Log Test
From myback1, myback2
With file = 3, norecovery
Go
Restore database Test
Filegroup = 'fg1'
From myback1, myback2
With file = 5, norecovery
Go
Restore Log Test
From myback1, myback2
With file = 6, recovery
Go

Therefore, when restoring a file or file group, after restoring the latest differential backup, the transaction log must be restored immediately. If no differential backup is performed after the complete backup, after the complete backup is restored, the transaction log must be original.