partial backup/restore

 http://www.sqlmag.com/blogs/sql-server-questions-answered/sql-server-questions-answered/tabid/1977/entryid/12771/Using-Partial-Database-Availability-for-Targeted-Restores.aspx

Listing 1: Code to Create a Database with Multiple File Groups

CREATE DATABASE ExampleDB;
GO 

ALTER DATABASE ExampleDB ADD FILEGROUP TestFileGroup1;
ALTER DATABASE ExampleDB ADD FILEGROUP TestFileGroup2;
GO 

ALTER DATABASE ExampleDB ADD FILE (
      NAME = TestFile1,
      FILENAME = 'C:\SQLskills\TestFile1.ndf',
      SIZE = 5MB)
TO FILEGROUP TestFileGroup1;
ALTER DATABASE ExampleDB ADD FILE (
      NAME = TestFile2,
      FILENAME = 'C:\SQLskills\TestFile2.ndf',
      SIZE = 5MB)
TO FILEGROUP TestFileGroup2;
GO 

CREATE TABLE ExampleDB..t1 (c1 INT) ON TestFileGroup1;
CREATE TABLE ExampleDB..t2 (c1 INT) ON TestFileGroup2;
GO

INSERT INTO ExampleDB..t1 VALUES (1);
INSERT INTO ExampleDB..t2 VALUES (2);
GO 

BACKUP DATABASE ExampleDB TO DISK = 'c:\SQLskills\ExampleDB.bck';

--BACKUP DATABASE Sales FILEGROUP = 'primary'  TO DISK = 'C:\SQLskills\ExampleDBPrimaryFG.bck'-- you can only backup the primary filegroup which contains all the definiton of objects,users...
GO

 

Now on a different system you can restore just the primary file group and the first file group. The key is to restore the primary file group first using WITH PARTIAL to let SQL Server know you aren’t restoring the entire database, as shown in Listing 2.
Listing 2: Restoring the Primary File Group Using WITH PARTIAL

 

RESTORE DATABASE ExampleDB
    FILEGROUP = 'primary'
FROM DISK = 'c:\SQLskills\ExampleDB.bck'
WITH PARTIAL, NORECOVERY;
GO

RESTORE DATABASE ExampleDB
    FILEGROUP = 'TestFileGroup1'
FROM DISK = 'c:\SQLskills\ExampleDB.bck'
WITH NORECOVERY;
GO

 

Next, you can restore any differential and/or transaction log backups to get the restored file groups to the desired point in time, and then bring the database online using the following code:
RESTORE DATABASE ExampleDB WITH RECOVERY;
If I query the database using the code in Listing 3, it will show me what’s online.

Listing 3: Code Used to Determine Which File Groups Are Online

SELECT [name], [state_desc] FROM ExampleDB.sys.database_files;

GO

name             state_desc

---------------- -----------------

ExampleDB        ONLINE

ExampleDB_log    ONLINE

TestFile1        ONLINE

TestFile2        RECOVERY_PENDING

Now, of course, I can only use the portions of the database that I’ve restored. If I try to access anything in an offline file group, I’ll get an error similar to the following:

SELECT * FROM ExampleDB..t2;

GO

Msg 8653, Level 16, State 1, Line 1

The query processor is unable to produce a plan for the table or view 't2' because the table resides in a filegroup which is not online.

 

 http://www.sqlmag.com/blogs/sql-server-questions-answered/sql-server-questions-answered/tabid/1977/entryid/12771/Using-Partial-Database-Availability-for-Targeted-Restores.aspx