文件/文件组备份 ------------------------------------------------------------------ (1)用于超大型数据库。 (2)只备份选定的文件或者文件组。 (3)必须同时作日志备份。 (4)还原时用文件/文件组备份和日志备份进行还原。 (5)备份量少,恢复速度快。 create database d5 on primary (name=d5_data1, filename='e:\data\d5\d5_data1.mdf', size=2MB), filegroup FG2 --创建数据库时创建filegroup FG2 (name=d5_data2, filename='e:\data\d5\d5_data2.ndf', --并将文件d5_data2放到FG2中 size=2Mb) log on (name=d5_log1, filename='e:\data\d5\d5_log1.ldf', size=2Mb) use d5 go alter database d5 add file (name=d5_data3, filename='e:\data\d5\d5_data5.ndf', size=2MB) to filegroup FG2 --将d5_data3加到文件组FG2中 alter database d5 add filegroup FG3 --增加文件组FG3 alter database d5 --将d5_data4加到文件组FG2中 add file (name=d5_data4, filename='e:\data\d5\d5_data4.ndf', size=2MB) to filegroup FG3 sp_helpdb d5 create table t1(c1 int not null,c2 char(10) not null) on [primary] --将不同表放到不同filegroup中 create table t2(c1 int not null,c2 char(10) not null) on FG2 create table t3(c1 int not null,c2 char(10) not null) on FG3 ---------------------------------------------------------------------- backup database d5 to bak5 with init,name='d5_full' --filegroup备份 backup database d5 filegroup='primary' to bak5 with backup log d5 to bak5 with backup database d5 filegroup='FG2' to bak5 with backup log d5 to bak5 with backup database d5 filegroup='FG3' to bak5 with backup log d5 to bak5 with ---------------------------------------------------------------------- backup database d5 to bak6 with init,name='d5_full' --file备份 backup database d5 file='d5_data1' to bak6 with backup log d5 to bak6 with backup database d5 file='d5_data2' to bak6 with backup log d5 to bak6 with backup database d5 file='d5_data3' to bak6 with backup log d5 to bak6 with backup database d5 file='d5_data4' to bak6 with backup log d5 to bak6 with restore headeronly from bak6 ======================================================================
-- 改为完整恢复模式 alter database [数据库名] set recovery full-- 针对文件组备份 backup database [数据库名] filegroup = '[文件组名]' to disk = '[备份文件]' with format,init
直接在简单恢复模式下进行文件组备份需用命令模式,但会报如下信息,backup database [数据库名] filegroup = '[文件组名]' to disk = '[备份文件]' with format,init--> This backup is a file backup of read-write data from a database that uses the simple recovery model. This is only appropriate if you plan to set the filegroup to read-only followed by a differential file backup. Consult Books Online for more information on managing read-only data for the simple recovery model. In particular, consider how partial backups are used. Processed 8 pages for database '[数据库名]', file 'xxx' on file 1. BACKUP DATABASE...FILE=<name> successfully processed 8 pages in 0.117 seconds (0.534 MB/sec).
------------------------------------------------------------------
(1)用于超大型数据库。
(2)只备份选定的文件或者文件组。
(3)必须同时作日志备份。
(4)还原时用文件/文件组备份和日志备份进行还原。
(5)备份量少,恢复速度快。
create database d5
on primary
(name=d5_data1,
filename='e:\data\d5\d5_data1.mdf',
size=2MB),
filegroup FG2 --创建数据库时创建filegroup FG2
(name=d5_data2,
filename='e:\data\d5\d5_data2.ndf', --并将文件d5_data2放到FG2中
size=2Mb)
log on
(name=d5_log1,
filename='e:\data\d5\d5_log1.ldf',
size=2Mb)
use d5
go
alter database d5
add file
(name=d5_data3,
filename='e:\data\d5\d5_data5.ndf',
size=2MB)
to filegroup FG2 --将d5_data3加到文件组FG2中
alter database d5 add filegroup FG3 --增加文件组FG3
alter database d5 --将d5_data4加到文件组FG2中
add file
(name=d5_data4,
filename='e:\data\d5\d5_data4.ndf',
size=2MB)
to filegroup FG3
sp_helpdb d5
create table t1(c1 int not null,c2 char(10) not null) on [primary] --将不同表放到不同filegroup中
create table t2(c1 int not null,c2 char(10) not null) on FG2
create table t3(c1 int not null,c2 char(10) not null) on FG3
----------------------------------------------------------------------
backup database d5 to bak5 with init,name='d5_full' --filegroup备份
backup database d5 filegroup='primary' to bak5 with
backup log d5 to bak5 with
backup database d5 filegroup='FG2' to bak5 with
backup log d5 to bak5 with
backup database d5 filegroup='FG3' to bak5 with
backup log d5 to bak5 with
----------------------------------------------------------------------
backup database d5 to bak6 with init,name='d5_full' --file备份
backup database d5 file='d5_data1' to bak6 with
backup log d5 to bak6 with
backup database d5 file='d5_data2' to bak6 with
backup log d5 to bak6 with
backup database d5 file='d5_data3' to bak6 with
backup log d5 to bak6 with
backup database d5 file='d5_data4' to bak6 with
backup log d5 to bak6 with
restore headeronly from bak6
======================================================================
-- 改为完整恢复模式
alter database [数据库名] set recovery full-- 针对文件组备份
backup database [数据库名] filegroup = '[文件组名]'
to disk = '[备份文件]' with format,init
to disk = '[备份文件]' with format,init-->
This backup is a file backup of read-write data from a database that uses the simple recovery model. This is only appropriate if you plan to set the filegroup to read-only followed by a differential file backup. Consult Books Online for more information on managing read-only data for the simple recovery model. In particular, consider how partial backups are used.
Processed 8 pages for database '[数据库名]', file 'xxx' on file 1.
BACKUP DATABASE...FILE=<name> successfully processed 8 pages in 0.117 seconds (0.534 MB/sec).