sqlserver backup and restore -- partital restore

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..