先贴一点老大的代码,继续关注。--以下代码简单地演示了如何进行文件组的备份及还原(在还原时,模拟了丢失第二次文件组备份文件的情况)。 --创建测试数据库 CREATE DATABASE db ON PRIMARY( NAME='db_data', FILENAME= 'c:\db_data.mdf'), FILEGROUP db_fg1( NAME = 'db_fg1_data', FILENAME = 'c:\db_fg1_data.ndf'), FILEGROUP db_fg2( NAME = 'db_fg2_data', FILENAME = 'c:\db_fg2_data.ndf') LOG ON( NAME='db_log', FILENAME ='c:\db.ldf') GO--在文件组db_fg1上创建表,并单独创建该文件组的备份 CREATE TABLE db.dbo.tb(id int) ON db_fg1 BACKUP DATABASE db FILEGROUP='db_fg1' TO DISK='c:\db_fg1.bak' WITH FORMAT GO在其他文件组上创建表 CREATE TABLE db.dbo.ta(id int) ON [PRIMARY] CREATE TABLE db.dbo.tc(id int) ON db_fg2 INSERT db.dbo.tb SELECT id FROM sysobjects --备份每个文件组,并且备份事务日志 BACKUP DATABASE db FILEGROUP='PRIMARY' TO DISK='c:\db_primary.bak' WITH FORMAT BACKUP DATABASE db FILEGROUP='db_fg1' TO DISK='c:\db_fg1_new.bak' WITH FORMAT BACKUP DATABASE db FILEGROUP='db_fg2' TO DISK='c:\db_fg2.bak' WITH FORMAT BACKUP LOG db TO DISK='c:\db_log.bak' WITH FORMAT GO--删除数据库 DROP DATABASE db GO--从文件组备份中恢复数据 RESTORE DATABASE db FILEGROUP='PRIMARY' FROM DISK='c:\db_primary.bak' WITH NORECOVERY,REPLACE RESTORE DATABASE db FILEGROUP='db_fg1' FROM DISK='c:\db_fg1.bak' WITH NORECOVERY RESTORE DATABASE db FILEGROUP='db_fg2' FROM DISK='c:\db_fg2.bak' WITH NORECOVERY RESTORE LOG db FROM DISK='c:\db_log.bak' WITH RECOVERY SELECT COUNT(*) FROM db.dbo.tb GO--删除测试数据库 DROP DATABASE db
19楼的朋友你把代码改成这样就能描述我的问题了.RESTORE DATABASE db FILEGROUP='PRIMARY' FROM DISK='c:\db_primary.bak' WITH RECOVERY,REPLACE RESTORE DATABASE db FILEGROUP='db_fg1' FROM DISK='c:\db_fg1.bak' WITH RECOVERY RESTORE DATABASE db FILEGROUP='db_fg2' FROM DISK='c:\db_fg2.bak' WITH RECOVERY --RESTORE LOG db FROM DISK='c:\db_log.bak' WITH RECOVERY--注释掉WITH RECOVERY后数据库仍然是不可用的也请大家看懂我的意思再贴代码,谢谢.
还原单个文件组后,日志文件.ldf并不存在,除非sql server 在restore database log with recovery 时能自动重建日志文件, 不过,sql server未提供此功能.个人认为ms提供可以备份单个文件组的目的在于减轻备份时的数据量或时间,以减轻空间上的不足或是性能上的压力.
--创建测试数据库
CREATE DATABASE db
ON PRIMARY(
NAME='db_data',
FILENAME= 'c:\db_data.mdf'),
FILEGROUP db_fg1(
NAME = 'db_fg1_data',
FILENAME = 'c:\db_fg1_data.ndf'),
FILEGROUP db_fg2(
NAME = 'db_fg2_data',
FILENAME = 'c:\db_fg2_data.ndf')
LOG ON(
NAME='db_log',
FILENAME ='c:\db.ldf')
GO--在文件组db_fg1上创建表,并单独创建该文件组的备份
CREATE TABLE db.dbo.tb(id int) ON db_fg1
BACKUP DATABASE db FILEGROUP='db_fg1' TO DISK='c:\db_fg1.bak' WITH FORMAT
GO在其他文件组上创建表
CREATE TABLE db.dbo.ta(id int) ON [PRIMARY]
CREATE TABLE db.dbo.tc(id int) ON db_fg2
INSERT db.dbo.tb SELECT id FROM sysobjects
--备份每个文件组,并且备份事务日志
BACKUP DATABASE db FILEGROUP='PRIMARY' TO DISK='c:\db_primary.bak' WITH FORMAT
BACKUP DATABASE db FILEGROUP='db_fg1' TO DISK='c:\db_fg1_new.bak' WITH FORMAT
BACKUP DATABASE db FILEGROUP='db_fg2' TO DISK='c:\db_fg2.bak' WITH FORMAT
BACKUP LOG db TO DISK='c:\db_log.bak' WITH FORMAT
GO--删除数据库
DROP DATABASE db
GO--从文件组备份中恢复数据
RESTORE DATABASE db FILEGROUP='PRIMARY' FROM DISK='c:\db_primary.bak' WITH NORECOVERY,REPLACE
RESTORE DATABASE db FILEGROUP='db_fg1' FROM DISK='c:\db_fg1.bak' WITH NORECOVERY
RESTORE DATABASE db FILEGROUP='db_fg2' FROM DISK='c:\db_fg2.bak' WITH NORECOVERY
RESTORE LOG db FROM DISK='c:\db_log.bak' WITH RECOVERY
SELECT COUNT(*) FROM db.dbo.tb
GO--删除测试数据库
DROP DATABASE db
RESTORE DATABASE db FILEGROUP='db_fg1' FROM DISK='c:\db_fg1.bak' WITH RECOVERY
RESTORE DATABASE db FILEGROUP='db_fg2' FROM DISK='c:\db_fg2.bak' WITH RECOVERY
--RESTORE LOG db FROM DISK='c:\db_log.bak' WITH RECOVERY--注释掉WITH RECOVERY后数据库仍然是不可用的也请大家看懂我的意思再贴代码,谢谢.
不过,sql server未提供此功能.个人认为ms提供可以备份单个文件组的目的在于减轻备份时的数据量或时间,以减轻空间上的不足或是性能上的压力.