use master;

create database [fs1] 
on primary (name = 'row_data', filename = 'C:\temp\fs1\fs1_row_data.mdf'),
   filegroup fsg contains filestream (name='fs_data', filename= 'C:\temp\fs1\fsg')
log on (name='log', filename = 'C:\temp\fs1\fs1_log.ldf')
with filestream (non_transacted_access=FULL, DIRECTORY_NAME = 'fs1');
go

use [fs1];
--наполнение данными
create table [dbo].[tbl_rows] (
      id int identity(1, 1) primary key clustered,
	  value varchar(255),
	  [uid] uniqueidentifier
) on [PRIMARY];

insert into [dbo].[tbl_rows] with(tablock) ([value], [uid]) 
  select top (1000000) c1.[name], newid() 
  from master.dbo.spt_values c1
      cross join master.dbo.spt_values c2; --наполнение inrow-data

create table [dbo].[tbl_fs] (
      [id] int identity(1,1) primary key,
	  [loc] uniqueidentifier not null rowguidcol  unique default newid(),
	  [data] varbinary(max) filestream null
);


insert into [dbo].[tbl_fs] with(tablock) ([data]) 
  select top (1000)
		   hashbytes('SHA2_512', cast(checksum(c1.[name], c2.[number], c2.[type]) as varchar(max)))
  from master.dbo.spt_values c1
      cross join master.dbo.spt_values c2; --наполнение данных FILESTREAM
--//наполнение данными
go

use master;
backup database [fs1] to disk = 'C:\temp\fs1_full.bak' with init; --создание полного бэкапа
restore filelistonly from disk = 'C:\temp\fs1_full.bak';
go

--создаем пустую бд
create database fs2 
on primary (name = 'fs2_data', filename = 'C:\temp\fs2\fs2_data.mdf')
log on (name = 'fs2_log', filename = 'C:\temp\fs2\fs2_log.ldf'); 
go

--восстанавливаем базу под замену файлов
restore database [fs2] 
from disk = 'C:\temp\fs1_full.bak'
with replace, 
move 'row_data' to 'C:\temp\fs2\fs2_data.mdf',
move 'log' to 'C:\temp\fs2\fs2_log.ldf',
move 'fs_data' to 'C:\temp\fs2\fsg', recovery, filestream (DIRECTORY_NAME='fs2');
go

--частичное восстановление PRIMARY
backup log [fs2] to disk = 'C:\temp\fs2_log1.bak' with norecovery;

restore database [fs2]
filegroup = 'PRIMARY'
from disk = 'C:\temp\fs1_full.bak'
with partial, norecovery;

restore database [fs2] with recovery, filestream (DIRECTORY_NAME='fs2'); --восстановление

use [fs2]
select top (10) * from tbl_rows;
select top (10) * from tbl_fs --даст ошибку
select type_desc, state_desc, name, size from sys.database_files
--//частичное восстановление PRIMARY


use master;
backup database [fs1] filegroup = 'fsg'
to disk = 'C:\temp\fs1_fsg.bak'
with init;

--частичное восстановление FILESTREAM
backup log [fs2] to disk = 'C:\temp\fs2_log2.bak' with norecovery;

restore database [fs2]
filegroup = 'PRIMARY'
from disk = 'C:\temp\fs1_full.bak'
with partial, norecovery;

restore database [fs2]
filegroup = 'fsg'
from disk = 'C:\temp\fs1_fsg.bak'
with norecovery;

backup log [fs1] to disk = 'C:\temp\fs1_log.bak';
restore log [fs2] from disk = 'C:\temp\fs1_log.bak' with filestream (DIRECTORY_NAME = 'fs2');
--//частичное восстановление FILESTREAM

select type_desc, name, state_desc, size from sys.master_files where database_id = db_id('fs1');
select type_desc, name, state_desc, size from sys.master_files where database_id = db_id('fs2');


drop database [fs2];
drop database [fs1];