Like a tratitional database, sqlserver not only could resotre full database but also do restore one part of it.
There are two important base physical file in sqlserver: Primary group and others group. Any situation, we must restore primary group at
least.
Here is an example.
USE [master]
GO
ALTER DATABASE [AdventureWorksDW2014] SET RECOVERY FULL WITH NO_WAIT
GO
ALTER DATABASE AdventureWorksDW2014 ADD FILEGROUP new_customers
ALTER DATABASE AdventureWorksDW2014 ADD FILEGROUP sales
GO
ALTER DATABASE AdventureWorksDW2014 ADD FILE
(NAME=‘mywind_data_1‘,FILENAME=N‘D:\Program Files\Microsoft SQL Server\mssql2014\mw.dat1‘) TO FILEGROUP new_customers
ALTER DATABASE AdventureWorksDW2014 ADD FILE
(NAME=‘mywind_data_2‘,FILENAME=N‘D:\Program Files\Microsoft SQL Server\mssql2014\mw.dat2‘) TO FILEGROUP sales
GO
BACKUP DATABASE [AdventureWorksDW2014] TO DISK = N‘D:\Program Files\Microsoft SQL Server\mssql2014\AdventureWorksDW2014.bak‘ WITH NOFORMAT, NOINIT, NAME = N‘AdventureWorksDW2014-Full Database Backup‘, SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10
GO
-----> generate a database and create two extra file group
USE AdventureWorksDW2014
GO
CREATE TABLE t1 (id int) ON new_customers
CREATE TABLE t2 (id int) ON sales
GO
BACKUP LOG [AdventureWorksDW2014] TO DISK = N‘D:\Program Files\Microsoft SQL Server\mssql2014\AdventureWorksDW2014.trn‘ WITH NOFORMAT, NOINIT, NAME = N‘AdventureWorksDW2014-Full Database Backup‘, SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10
---> create two tables and each table on separate file groups
-- new_customers is not online in new database (must primary group & logfile are include)
RESTORE DATABASE AdventureWorksDW2014_part
FILEGROUP = ‘PRIMARY‘, FILEGROUP = ‘sales‘
FROM DISK = N‘D:\Program Files\Microsoft SQL Server\mssql2014\AdventureWorksDW2014.bak‘
WITH FILE=1,NORECOVERY,PARTIAL,
MOVE ‘AdventureWorksDW2014_Data‘ TO N‘D:\Program Files\Microsoft SQL Server\mssql2014\new\AdventureWorksDW2014_Data.mdf‘,
MOVE ‘AdventureWorksDW2014_Log‘ TO N‘D:\Program Files\Microsoft SQL Server\mssql2014\new\AdventureWorksDW2014_Log.ldf‘,
MOVE ‘mywind_data_2‘ TO N‘D:\Program Files\Microsoft SQL Server\mssql2014\new\mw2.dat2‘
GO
RESTORE LOG AdventureWorksDW2014_part
FROM DISK = N‘D:\Program Files\Microsoft SQL Server\mssql2014\AdventureWorksDW2014.trn‘ WITH FILE=1,RECOVERY
GO
---> we could not need restore all filegroups beside primary group. You see ? That is possible/
So, Please make your primary group at less size as your can..
sqlserver backup and restore -- partital restore
原文：https://www.cnblogs.com/partition-liu/p/12317872.html