Add a filegroup

create database TestPrimaryDb on primary
(
	Name='TestPrimaryDb',
	FILENAME ='D:\data\TestPrimaryDb.mdf'
) 
,filegroup fg1
(
Name='TestPrimaryDbfg1',
	FILENAME ='D:\data\TestPrimaryDbfg1.mdf'
)
log on
(
Name='TestPrimaryDb_log',
	FILENAME ='D:\data\TestPrimaryDb.ldf'
)

exec sp_detach_db TestPrimaryDb;

create database TestPrimaryDb
on (FILENAME ='D:\data\TestPrimaryDbfg1.mdf')
for attach
--The FOR ATTACH option requires that at least the primary file be specified.

exec sp_attach_db TestPrimaryDb, @filename1=N'D:\data\TestPrimaryDb.mdf',@filename2=N'D:\data\TestPrimaryDbfg1.mdf',@filename3=N'D:\data\TestPrimaryDb.ldf'

use TestPrimaryDb;
go

create table UserInfo
(
	ID int identity(1,1) primary key,
	Name varchar(10) default 'aaa'
)on fg1

insert into UserInfo values('b');

select * from UserInfo;

alter database TestPrimaryDb set single_user with rollback immediate;

use master;
go
exec sp_detach_db TestPrimaryDb;

alter database Test
add filegroup fg1


alter database Test
add file
(
	Name='TestPrimaryDbfg1',
	FILENAME ='D:\data\TestPrimaryDbfg1.mdf'
) to filegroup fg1;
--Cannot create file 'D:\data\TestPrimaryDbfg1.mdf' because it already exists. Change the file path or the file name, and retry the operation.

alter database Test
add file
(
	Name='TestPrimaryDbfg1',
	FILENAME ='D:\data\afdafd.mdf'
) to filegroup fg1;

alter database Test
modify file
(
	Name='TestPrimaryDbfg1',
	FILENAME ='D:\data\TestPrimaryDbfg1.mdf'
) 

use Test;
go

select * from UserInfo;


select fg=filegroup_name(a.data_space_id), tbl=object_name(p.object_id), idx=i.name
from sys.allocation_units a
inner join sys.partitions p
on p.partition_id = a.container_id
and p.object_id > 1024 -- arbitary, just to filter out system objects 
and a.type = 2
left join sys.indexes i
on i.object_id = p.object_id
and i.index_id = p.index_id
union all
select fg=filegroup_name(a.data_space_id), tbl=object_name(p.object_id), idx=i.name
from sys.allocation_units a
inner join sys.partitions p
on p.hobt_id = a.container_id
and p.object_id > 1024 -- arbitary, just to filter out system objects 
and a.type in (1, 3)
left join sys.indexes i
on i.object_id = p.object_id
and i.index_id = p.index_id


/*
Cannot find the table exists on fg1 filegroup any more.
*/

 Concern 1: one database has two mdf;

Concern 2: why the table missing.