CREATE DATABASE [test]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'test', FILENAME = N'E:\MSSQL\test.mdf' , SIZE = 5120KB , FILEGROWTH = 1024KB ), 
 FILEGROUP [ARCH] 
( NAME = N'test_arch', FILENAME = N'E:\MSSQL\test_arch.ndf' , SIZE = 5120KB , FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'test_log', FILENAME = N'E:\MSSQL\test_log.ldf' , SIZE = 2048KB , FILEGROWTH = 10%)
GO


use [test]
GO
CREATE PARTITION FUNCTION myRangePF1 (int)  
AS RANGE LEFT FOR VALUES (1, 100, 1000);  
GO  
CREATE PARTITION SCHEME myRangePS1  
AS PARTITION myRangePF1  
TO ([ARCH], [ARCH], [ARCH], [PRIMARY]);  
GO

create table dbo.test_table (a int)

create clustered index CIX on dbo.test_table (a)
ON myRangePS1 (a);

insert into dbo.test_table
select 0 union all 
select 50 union all 
select 500 union all 
select 5000


ALTER DATABASE [test] MODIFY FILEGROUP [ARCH] READONLY


use test
go
SELECT PA.OBJECT_ID, FG.name,*
FROM sys.filegroups FG
    INNER JOIN sys.allocation_units AU ON AU.data_space_id = FG.data_space_id
    INNER JOIN sys.partitions PA ON PA.partition_id = AU.container_id
WHERE PA.OBJECT_ID = OBJECT_ID('dbo.test_table')


BACKUP DATABASE [test] TO  DISK = N'Z:\test.bak'
WITH NOFORMAT, NOINIT, 
NAME = N'test-Full Database Backup', SKIP, NOREWIND, NOUNLOAD
GO

use [master]
go
RESTORE DATABASE [test_part] 
FILEGROUP = N'PRIMARY'
FROM  DISK = N'Z:\test.bak'
WITH  FILE = 1,
  MOVE N'test' TO N'Z:\test_part.mdf',
  MOVE N'test_arch' TO N'Z:\test_part_0.ndf',
  MOVE N'test_log' TO N'Z:\test_part_1.ldf',  NOUNLOAD,  REPLACE,  STATS = 10
GO

use [test_part]
go
select * from [dbo].[test_table]

-- ARCH в RECOVERY_PENDING
SELECT 
 f.name file_group, 
 d.name file_name, 
 d.state_desc file_state
FROM sys.filegroups f
JOIN sys.database_files d
 ON f.data_space_id = d.data_space_id;
GO



USE [test_part]
GO
ALTER DATABASE [test_part]  REMOVE FILE [test_arch]
GO
ALTER DATABASE [test_part] REMOVE FILEGROUP [ARCH]
GO


-- ARCH в DEFUNCT
SELECT 
 f.name file_group, 
 d.name file_name, 
 d.state_desc file_state
FROM sys.filegroups f
JOIN sys.database_files d
 ON f.data_space_id = d.data_space_id;
GO