单独还原个别文件操作

创建数据库与多个文件组,在各个文件组中放置不同的数据表,故意损坏其中一个文件后,

通过设置,可以让改数据库内其它完好的文件组继续提供访问,并经过还原备份数据,可以单独回复该数据文件与文件组

 

1.创建NWind测试数据库,并添加3个文件组

USE master GO -- DROP DATABASE NWind CREATE DATABASE NWind ON PRIMARY(NAME = N'NWind_P', FILENAME = N'D:/SqlServer2008Test/NWind_P.mdf'), FILEGROUP FileGroupA(NAME = N'NWind_A', FILENAME = N'D:/SqlServer2008Test/NWind_A.ndf'), FILEGROUP FileGroupB(NAME = N'NWind_B', FILENAME = N'D:/SqlServer2008Test/NWind_B.ndf'), FILEGROUP FileGroupC(NAME = N'NWind_C', FILENAME = N'D:/SqlServer2008Test/NWind_C.ndf') GO   

 

 

2.在不同的FileGroup中分别放置一个数据表

USE NWind GO CREATE TABLE TbP(c1 INT, c2 NVARCHAR(10)) ON [PRIMARY] CREATE TABLE TbA(c1 INT, c2 NVARCHAR(10)) ON FileGroupA CREATE TABLE TbB(c1 INT, c2 NVARCHAR(10)) ON FileGroupB CREATE TABLE TbC(c1 INT, c2 NVARCHAR(10)) ON FileGroupC GO

3.查看各个数据表所属的FileGroup

SELECT objs.schemaN AS N'架构', objs.tableN AS N'数据表', das.name AS N'文件组' FROM NWind.sys.data_spaces das INNER JOIN (SELECT DISTINCT object_id, data_space_id FROM NWind.sys.indexes WHERE type IN('0', '1')) idx ON das.data_space_id = idx.data_space_id INNER JOIN (SELECT sys.objects.name AS 'tableN', object_id, sys.schemas.name AS 'schemaN' FROM NWind.sys.objects INNER JOIN NWind.sys.schemas ON sys.objects.schema_id = sys.schemas.schema_id WHERE type = 'U') objs ON objs.object_id = idx.object_id ORDER BY 3

4.分析各个数据文件的属性

SELECT name, physical_name, state_desc, is_read_only, read_only_lsn FROM NWind.sys.database_files -- SELECT * FROM sys.database_files 

 

5.向数据库中插入数据

USE NWind GO INSERT INTO TbP VALUES(1, 'P') INSERT INTO TbA VALUES(1, 'A') INSERT INTO TbB VALUES(1, 'B') INSERT INTO TbC VALUES(1, 'C') GO  

6.分析各个表中的数据

USE NWind GO SELECT * FROM TbP UNION ALL SELECT * FROM TbA UNION ALL SELECT * FROM TbB UNION ALL SELECT * FROM TbC GO  

 

 

7.备份NWind数据库

 

BACKUP DATABASE NWind TO DISK = N'D:/SqlServer2008Test/NWind.bak' WITH COMPRESSION, INIT

 

8.暂停MSSQLSERVER服务,并删掉NWind_B.ndf文件模拟数据库损坏

 

 

9.出现错误

 

 

10.检查NWind数据库处于RECOVERY_PENDING状态

 

SELECT name, state_desc FROM sys.databases

11.设置文件NWind_B为OFFLINE,并设置NWind数据库状态为ONLINE

ALTER DATABASE NWind MODIFY FILE (NAME = N'NWind_B', OFFLINE) GO ALTER DATABASE NWind SET ONLINE GO

 

12.分析各个数据文件的在线状态

 

USE NWind GO SELECT name, physical_name, state_desc, is_read_only, read_only_lsn FROM NWind.sys.database_files GO

 

13.分析各个表的数据,查询TbB时候出错

 

USE NWind GO SELECT * FROM TbP SELECT * FROM TbA SELECT * FROM TbB SELECT * FROM TbC GO

14.向数据库中插入数据,向TbB中插入数据时出错

 

USE NWind GO INSERT INTO TbP VALUES(2, 'P') INSERT INTO TbA VALUES(2, 'A') INSERT INTO TbB VALUES(2, 'B') INSERT INTO TbC VALUES(2, 'C') GO

15.备份日志文件

BACKUP LOG NWind TO DISK = N'D:/SqlServer2008Test/NWind.bak' WITH NAME = N'NWind 日志备份', COMPRESSION, NO_TRUNCATE

NO_TRUNCATE 指定不截断日志,并使数据库引擎尝试执行备份,而不考虑数据库的状态.

因此,使用 NO_TRUNCATE 执行的备份可能具有不完整的元数据.该选项允许在数据库损坏时备份日志.

BACKUP LOG 的 NO_TRUNCATE 选项相当于同时指定 COPY_ONLY 和 CONTINUE_AFTER_ERROR.

如果不使用 NO_TRUNCATE 选项,则数据库必须联机.

 

16.查询备份文件的头信息

RESTORE HEADERONLY FROM DISK = N'D:/SqlServer2008Test/NWind.bak'

 

17.查询备份文件的文件信息

RESTORE FILELISTONLY FROM DISK = N'D:/SqlServer2008Test/NWind.bak'

 

18.还原损坏的数据库

USE master GO RESTORE DATABASE NWind FILE = N'NWind_B' FROM DISK = N'D:/SqlServer2008Test/NWind.bak' WITH RECOVERY GO

 

19.分析各个数据文件的属性,Nwind_B处于RESTORING状态

USE NWind GO SELECT name, physical_name, state_desc, is_read_only, read_only_lsn FROM NWind.sys.database_files GO

20.还原NWind的日志文件

USE master GO RESTORE LOG NWind FROM DISK = N'D:/SqlServer2008Test/NWind.bak' WITH FILE = 2, RECOVERY GO

21.分析各个数据文件的属性

USE NWind GO SELECT name, physical_name, state_desc, is_read_only, read_only_lsn FROM NWind.sys.database_files GO

 

22.分析各个表中的数据

USE NWind GO SELECT * FROM TbP UNION ALL SELECT * FROM TbA UNION ALL SELECT * FROM TbB UNION ALL SELECT * FROM TbC GO