问备份和还原SQL server文件和文件组

EN

RESTORE HEADERONLY FROM DISK = 'D:\sec_file_ndf.bak'

RESTORE FILELISTONLY FROM DISK= 'D:\sec_file_ndf.bak' 

if db_id('test') is not null drop database test;
go

CREATE DATABASE test ON  PRIMARY 
( NAME = N'prime1', 
  FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\test.mdf' , 
  SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) 
 LOG ON 
( NAME = N'test_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\test_log.ldf' , 
SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%);
go

alter database test set recovery FULL;
go

backup database test filegroup = 'PRIMARY' 
to disk = 'Z:\backups\default\test_primaryFG1.bak';
go

alter database test add file
( NAME = N'prime2', 
  FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\test_prime2.ndf' , 
  SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB );
go 

-- second connection:
use test;
go  
create table dbo.t(id int);
go

-- first connection:  
backup database test file = 'prime1' 
to disk = 'Z:\backups\default\test_prime1.bak';
go

-- second connection:
create table dbo.t1(id int);
insert into dbo.t1(id) values(1);
go


-- first connection:
backup database test file = 'prime2' 
to disk = 'Z:\backups\default\test_prime2.bak'

-----------------------------------
backup log test 
to disk = 'Z:\backups\default\test_tail.trn' with norecovery;

restore database test file = 'prime2'
from disk = 'Z:\backups\default\test_prime2.bak'
with norecovery;

restore database test
from disk = 'Z:\backups\default\test_tail.trn' with recovery;

-----------------------------------
-- just another restore starting with FD restore as OP did:
restore database test 
from disk = 'Z:\backups\default\test_primaryFG1.bak' with replace, norecovery;

restore database test file = 'prime2' 
from disk = 'Z:\backups\default\test_prime2.bak' with norecovery


restore database test
from disk = 'Z:\backups\default\test_tail.trn' with recovery;