[Distinct] SQL File Group Backup and Restoration

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

If a database is large, it usually contains multiple file groups. Because backup and restoration are costly and time consuming, therefore, File Group Backup and restoration are another option.

When creating a file group with multiple file groups, we recommend that you put user data in the secondary file group, that is, to set one of the file groups as the default, instead of using primary as the default file group:

Create Database Test

On Primary

(Name = test_mdf,

Filename = 'd: \ SQL _data \ test. MDF'

Size = 4 MB,

Maxsize = unlimited,

Filegrowth = 10% ),

Filegroup FG1 default

(Name = test_ndf,

Filename = 'd: \ sec_data \ test. NDF ',

Size = 4 MB,

Maxsize = unlimited,

Filegrowth = 10%)

Log On
(Name = test_log,
Filename = 'd: \ log \ test. LDF ',
Size = 4 MB,
Maxsize = unlimited,
Filegrowth = 10%)

In the above example, the primary file group primary will only contain system tables and objects, excluding user database files, which is more conducive to the backup and restoration of the file group.

In addition, you can also specify the file group to which the created table is stored (sometimes there are many file groups)

Create Table <table_name> (...) on <File Group Name>

This may be used in database design.

1. Back up database file groups

(1) create a complete backup of the file group

Use master
Go
Backup database Test
Filegroup = 'fg1'
To myback1, myback2
With name = 'test _ fullbackup_20090524'

(2) create differential backup for file groups

Use master
Go
Backup database Test
Filegroup = 'fg1'
To myback1, myback2
With differential, name = 'test _ diffbackup_20090524'

(3) create a log backup

Use master

Go

Backup Log Test

To myback1, myback2

With name = 'test _ logbackup_20090524'

2. Restore database file groups

Note that the last log backup must be performed before restoring a file or file group backup, this is because you need to restore the file or file group to the same state as other files or file groups in the database.If the last log backup cannot be obtained, a single file or file group cannot be restored separately, but the entire database must be restored.

(1) back up the end log

This is the same as the backup log.

Use master

Go

Backup Log Test

To myback1, myback2

With name = 'test _ taillogbackup_20090524'

(2) restore a file group

First, check the backup information.

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

Note: The transaction log backup before differential backup does not need to be restored.

To back up or restore a file, change filegroup to file.

3. Common file or file group backup and restoration policies

Assume that there are only two file groups, primary and FG1.

(1) complete file group backup for primary on Saturday evening (as a base backup)

(2) complete file group backup for FG1 on Sunday evening (as a base backup)

(3) Back up differential file groups for primary on Tuesday evening

(4) Differential File Group Backup for FG1 on Wednesday evening

(5) Back up differential file groups for primary on Thursday evening

(6) Differential File Group Backup for FG1 on Friday evening

(7) Back up transaction logs every 30 minutes

When a fault is restored

(1) restore the base backup of the two file groups

(2) restore the latest differential backup of primary

(3) restore the log backup after the latest differential backup of primary

(4) restore the latest differential backup of FG1

(5) restore the log backup after the latest differential backup of FG1