System.Data.SqlClient.SqlError: The media set has 2 media families but only 1 are provided. All members must be provided. (Microsoft.SqlServer.Smo)这个是错误信息,我想如果能删除掉这个约束就应当没问题了!可是我没找到如何删除的办法!
-- 查询所有表的文件组信息 select data_located_on_filegroup = fg.groupname, table_name = obj.name from sysfilegroups fg, sysindexes idx, sysobjects obj where idx.id = obj.id and obj.type in ('S ','U ') and idx.indid < 2 and idx.groupid = fg.groupid order by fg.groupname -- 检查指定文件组中的所有表(在当前数据库中)的分配和结构完整性 dbcc checkfilegroup -- 查询数据库的基本信息,包括有数据文件和日志文件 sp_helpdb 数据库名 -- 查询系统的文件组信息 select * from sysfilegroups sp_helpfilegroup -- 查询编号为id的对象的文件组信息 exec sp_objectfilegroup id
/*添加文件组fg*/ alter database test add filegroup fg/*添加对应的数据文件*/ alter database test add file ( name = data_file_01, filename = 'd:\mssql_data\test_1.ndf', size = 300mb, maxsize = 8912mb, filegrowth = 50mb) to filegroup fgalter database test add file ( name = data_file_02, filename = 'd:\mssql_data\test_1.ndf', size = 300mb, maxsize = 8912mb, filegrowth = 50mb) to filegroup fg/*删除数据文件*/ alter database test_raid remove file data_file_01/*删除文件组*/ alter database test_raid remove filegroup fg如果有表属于该文件组,则无法删除该文件组 例如: alter database test_raid remove filegroup [primary] error:The filegroup 'primary' cannot be removed because it is not empty.
从你的错误信息中搜到: the reason was that my backup was splitst in two files, one in backup device and one in ":\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\[database].bak"first i added the file from the directory above and then my backup device and then problem was solved 看看你的备份文件是不是有两个,一个是备份文件,一个是备份驱动,还原时需要这两个文件就可以搞定了,试试吧
-- 查询所有表的文件组信息
select
data_located_on_filegroup = fg.groupname,
table_name = obj.name
from sysfilegroups fg, sysindexes idx, sysobjects obj
where idx.id = obj.id
and obj.type in ('S ','U ')
and idx.indid < 2
and idx.groupid = fg.groupid
order by fg.groupname
-- 检查指定文件组中的所有表(在当前数据库中)的分配和结构完整性
dbcc checkfilegroup
-- 查询数据库的基本信息,包括有数据文件和日志文件
sp_helpdb 数据库名
-- 查询系统的文件组信息
select * from sysfilegroups sp_helpfilegroup
-- 查询编号为id的对象的文件组信息
exec sp_objectfilegroup id
/*添加文件组fg*/
alter database test
add filegroup fg/*添加对应的数据文件*/
alter database test
add file
( name = data_file_01,
filename = 'd:\mssql_data\test_1.ndf',
size = 300mb,
maxsize = 8912mb,
filegrowth = 50mb)
to filegroup fgalter database test
add file
( name = data_file_02,
filename = 'd:\mssql_data\test_1.ndf',
size = 300mb,
maxsize = 8912mb,
filegrowth = 50mb)
to filegroup fg/*删除数据文件*/
alter database test_raid remove file data_file_01/*删除文件组*/
alter database test_raid remove filegroup fg如果有表属于该文件组,则无法删除该文件组
例如:
alter database test_raid remove filegroup [primary]
error:The filegroup 'primary' cannot be removed because it is not empty.
the reason was that my backup was splitst in two files, one in backup device and one in ":\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\[database].bak"first i added the file from the directory above and then my backup device and then problem was solved 看看你的备份文件是不是有两个,一个是备份文件,一个是备份驱动,还原时需要这两个文件就可以搞定了,试试吧
你的是操作组,不过删除还要先删除数据库文件。那么原来的操作都是无用功了!还是谢谢!
问题解决:
建立数据库的时候选择建立simple 模式。备份还原的时候也是simple 模式。
点击数据库属性,点击options ,然后选择将Recovery mode 选择成simple 模式,这样备份的时候就会自动将filegroup 屏蔽掉。
给分!