Differential and Partial Differential backups with Partitioned tables and Read only FileGroups

Here is a quick write up w/ little commentary.  This is a corollary to an earlier article.  I wanted to see how differential backups are affected by partitioning and read only.  See the comments to see where we are.  If I have time in the future I will try to hash it out but right now it is get it up as this or don’t get it online at all.  Hope this helps someone.

The idea isn’t to run this whole block of code.   Run it chunk by chunk and see what happens. Standard disclaimers, if you don’t know what this is doing, it isn’t my problem if you bork your system.

This was all done on SQL 2008R2.

--Setup test dbs to use for walkthrough

use master

exec master.dbo.usp_killconnections 'testdb'

IF EXISTS (

SELECT name

FROM sys.databases

WHERE name = N'TestDB')

DROP DATABASE TestDB;

GO

CREATE DATABASE TestDB

ON PRIMARY

(NAME='TestDB_Part1', FILENAME='c:\data\TestDB_Part1.mdf',

SIZE=3, MAXSIZE=100, FILEGROWTH=1 ),

FILEGROUP TestDB_Part2

(NAME = 'TestDB_Part2', FILENAME ='c:\data\TestDB_Part2.ndf',

SIZE = 2, MAXSIZE=100, FILEGROWTH=1 ),

FILEGROUP TestDB_Part3

(NAME = 'TestDB_Part3', FILENAME ='c:\data\TestDB_Part3.ndf',

SIZE = 2, MAXSIZE=100, FILEGROWTH=1 ),

FILEGROUP TestDB_Part4

(NAME = 'TestDB_Part4', FILENAME ='c:\data\TestDB_Part4.ndf',

SIZE = 2, MAXSIZE=100, FILEGROWTH=1 ),

FILEGROUP TestDB_Part5

(NAME = 'TestDB_Part5', FILENAME ='c:\data\TestDB_Part5.ndf',

SIZE = 2, MAXSIZE=100, FILEGROWTH=1 );

GO

USE TestDB;

GO

--- Step 2 : Create Partition Range Function

CREATE PARTITION FUNCTION TestDB_PartitionRange (INT)

AS RANGE LEFT FOR

VALUES (10,20,30,40);

GO

USE TestDB;

GO

--- Step 3 : Attach Partition Scheme to FileGroups

CREATE PARTITION SCHEME TestDB_PartitionScheme

AS PARTITION TestDB_PartitionRange

TO ([PRIMARY], TestDB_Part2, TestDB_Part3, TestDB_Part4, TestDB_Part5);

GO

--- Step 4 : Create Table with Partition Key and Partition Scheme

CREATE TABLE TestTable

(ID INT NOT NULL,

Date DATETIME)

ON TestDB_PartitionScheme (ID);

GO

--- Step 5 : (Optional/Recommended) Create Index on Partitioned Table

CREATE UNIQUE CLUSTERED INDEX IX_TestTable

ON TestTable(ID)

ON TestDB_PartitionScheme (ID);

GO

--- Step 6 : Insert Data in Partitioned Table

INSERT INTO TestTable (ID, Date) -- Inserted in Partition 1

VALUES (1,GETDATE());

INSERT INTO TestTable (ID, Date) -- Inserted in Partition 2

VALUES (11,GETDATE());

INSERT INTO TestTable (ID, Date) -- Inserted in Partition 2

VALUES (12,GETDATE());

INSERT INTO TestTable (ID, Date) -- Inserted in Partition 4

VALUES (32,GETDATE());

INSERT INTO TestTable (ID, Date) -- Inserted in Partition 5

VALUES (42,GETDATE());

INSERT INTO TestTable (ID, Date) -- Inserted in Partition 5

VALUES (52,GETDATE());

INSERT INTO TestTable (ID, Date) -- Inserted in Partition 3

VALUES (22,GETDATE());

GO

--- Step 7 : Test Data from TestTable

SELECT *

FROM TestTable;

GO

--- Step 8 : Verify Rows Inserted in Partitions

SELECT *

FROM sys.partitions

WHERE OBJECT_NAME(OBJECT_ID)='TestTable';

GO

-- set FG 2 to read only

ALTER DATABASE [TestDB] MODIFY FILEGROUP [TestDB_Part2] READONLY

GO

-- Normal Full backup

BACKUP DATABASE [TestDB]

TO  DISK = N'c:\data\TestDB_Full.bak'

WITH NOFORMAT, INIT, 

SKIP, NOREWIND, NOUNLOAD,  STATS = 10

GO

-- Backup rw and ro separately

BACKUP DATABASE [TestDB]

READ_WRITE_FILEGROUPS

TO  DISK = N'c:\data\TestDB_RW.bak'

WITH NOFORMAT, INIT, 

SKIP, NOREWIND, NOUNLOAD,  STATS = 10

GO

BACKUP DATABASE [TestDB]

FILEGROUP = N'TestDB_Part2'

TO  DISK = N'c:\data\TestDB_RO_Part2.bak'

WITH NOFORMAT, INIT, 

SKIP, NOREWIND, NOUNLOAD,  STATS = 10

GO

-- add data

use testdb

INSERT INTO TestTable (ID, Date) -- Inserted in Partition 4

VALUES (24,GETDATE());

INSERT INTO TestTable (ID, Date) -- Inserted in Partition 5

VALUES (44,GETDATE());

INSERT INTO TestTable (ID, Date) -- Inserted in Partition 5

VALUES (54,GETDATE());

-- Backup DIFF

BACKUP DATABASE [TestDB]

TO  DISK = N'c:\data\TestDB_DIFF.bak'

WITH NOFORMAT, INIT, DIFFERENTIAL  ,

SKIP, NOREWIND, NOUNLOAD,  STATS = 10

GO

--backup Partial (RW) diff

BACKUP DATABASE [TestDB]

READ_WRITE_FILEGROUPS

TO  DISK = N'c:\data\TestDB_DIFFPartial.bak'

WITH NOFORMAT, INIT, DIFFERENTIAL  ,

SKIP, NOREWIND, NOUNLOAD,  STATS = 10

GO

-- list backup files.  We did not backup the read only file group in either diff

RESTORE FILELISTONLY from disk =  N'c:\data\TestDB_Diff.bak'

RESTORE FILELISTONLY from disk =  N'c:\data\TestDB_DiffPartial.bak'

--restore RW portion w/ no recovery

use master

exec master.dbo.usp_killconnections [TestDB2]

drop database testdb2

RESTORE DATABASE [TestDB2]

read_write_filegroups

  FROM  DISK = N'c:\data\TestDB_RW.bak'  WITH  FILE = 1, 

      MOVE N'TestDB_Part1' TO N'c:\data\TestDB2_Part1.mdf', 

      MOVE N'TestDB_Part2' TO N'c:\data\TestDB2_Part2.mdf', 

      MOVE N'TestDB_Part3' TO N'c:\data\TestDB2_Part3.mdf', 

      MOVE N'TestDB_Part4' TO N'c:\data\TestDB2_Part4.mdf', 

      MOVE N'TestDB_Part5' TO N'c:\data\TestDB2_Part5.mdf', 

      MOVE N'TestDB_log' TO N'c:\data\TestDB2_log.ldf',

  REPLACE, NOUNLOAD,  STATS = 10, NORECOVERY

GO

-- see files offline

select * from sys.master_files  where database_id = (select database_id from sys.databases where name = 'testdb2')

-- restore RO

use master

RESTORE DATABASE [TestDB2]  FILEGROUP = 'TestDB_Part2'  FROM

DISK = N'c:\data\TestDB_RO_Part2.bak'   WITH 

MOVE 'TestDB_Part2' TO N'c:\data\TestDB2_Part2.mdf'

--hmm, some warnings:

----The roll forward start point is now at log sequence number (LSN) 20000000057700001. Additional roll forward past LSN 20000000061400001 is required to complete the restore sequence.

----This RESTORE statement successfully performed some actions, but the database could not be brought online because one or more RESTORE steps are needed. Previous messages indicate reasons why recovery cannot occur at this point.

-- All files in restoring now...

select * from sys.master_files  where database_id = (select database_id from sys.databases where name = 'testdb2')

--restore partial dff

RESTORE DATABASE [TestDB2]

read_write_filegroups

  FROM  DISK = N'c:\data\TestDB_DIFFPartial.bak'  WITH  FILE = 1, 

      MOVE N'TestDB_Part1' TO N'c:\data\TestDB2_Part1.mdf', 

      MOVE N'TestDB_Part2' TO N'c:\data\TestDB2_Part2.mdf', 

      MOVE N'TestDB_Part3' TO N'c:\data\TestDB2_Part3.mdf', 

      MOVE N'TestDB_Part4' TO N'c:\data\TestDB2_Part4.mdf', 

      MOVE N'TestDB_Part5' TO N'c:\data\TestDB2_Part5.mdf', 

      MOVE N'TestDB_log' TO N'c:\data\TestDB2_log.ldf',

  REPLACE, NOUNLOAD,  STATS = 10

GO

-- All files online now...

select * from sys.master_files  where database_id = (select database_id from sys.databases where name = 'testdb2')

--let's try w/ diff before the ro

--restore RW portion w/ no recovery

use master

exec master.dbo.usp_killconnections [TestDB2]

drop database testdb2

RESTORE DATABASE [TestDB2]

read_write_filegroups

  FROM  DISK = N'c:\data\TestDB_RW.bak'  WITH  FILE = 1, 

      MOVE N'TestDB_Part1' TO N'c:\data\TestDB2_Part1.mdf', 

      MOVE N'TestDB_Part2' TO N'c:\data\TestDB2_Part2.mdf', 

      MOVE N'TestDB_Part3' TO N'c:\data\TestDB2_Part3.mdf', 

      MOVE N'TestDB_Part4' TO N'c:\data\TestDB2_Part4.mdf', 

      MOVE N'TestDB_Part5' TO N'c:\data\TestDB2_Part5.mdf', 

      MOVE N'TestDB_log' TO N'c:\data\TestDB2_log.ldf',

  REPLACE, NOUNLOAD,  STATS = 10, NORECOVERY

GO

-- see files offline

select * from sys.master_files  where database_id = (select database_id from sys.databases where name = 'testdb2')

--restore partial diff and bring online

RESTORE DATABASE [TestDB2]

read_write_filegroups

  FROM  DISK = N'c:\data\TestDB_DIFFPartial.bak'  WITH  FILE = 1, 

      MOVE N'TestDB_Part1' TO N'c:\data\TestDB2_Part1.mdf', 

      MOVE N'TestDB_Part2' TO N'c:\data\TestDB2_Part2.mdf', 

      MOVE N'TestDB_Part3' TO N'c:\data\TestDB2_Part3.mdf', 

      MOVE N'TestDB_Part4' TO N'c:\data\TestDB2_Part4.mdf', 

      MOVE N'TestDB_Part5' TO N'c:\data\TestDB2_Part5.mdf', 

      MOVE N'TestDB_log' TO N'c:\data\TestDB2_log.ldf',

  REPLACE, NOUNLOAD,  STATS = 10

GO

-- still some warnings

----The file "TestDB_Part2" was not fully restored by a database or file restore. The entire file must be successfully restored before applying this backup set.

----This RESTORE statement successfully performed some actions, but the database could not be brought online because one or more RESTORE steps are needed. Previous messages indicate reasons why recovery cannot occur at this point.

-- restore RO

use master

RESTORE DATABASE [TestDB2]  FILEGROUP = 'TestDB_Part2'  FROM

DISK = N'c:\data\TestDB_RO_Part2.bak'   WITH 

MOVE 'TestDB_Part2' TO N'c:\data\TestDB2_Part2.mdf'

-- All files online now...

select * from sys.master_files  where database_id = (select database_id from sys.databases where name = 'testdb2')

-- ok, for completeness, what is the difference if I use the 'full' differential?  (ie the non partial one)

--restore RW portion w/ no recovery

use master

exec master.dbo.usp_killconnections [TestDB2]

drop database testdb2

RESTORE DATABASE [TestDB2]

read_write_filegroups

  FROM  DISK = N'c:\data\TestDB_RW.bak'  WITH  FILE = 1, 

      MOVE N'TestDB_Part1' TO N'c:\data\TestDB2_Part1.mdf', 

      MOVE N'TestDB_Part2' TO N'c:\data\TestDB2_Part2.mdf', 

      MOVE N'TestDB_Part3' TO N'c:\data\TestDB2_Part3.mdf', 

      MOVE N'TestDB_Part4' TO N'c:\data\TestDB2_Part4.mdf', 

      MOVE N'TestDB_Part5' TO N'c:\data\TestDB2_Part5.mdf', 

      MOVE N'TestDB_log' TO N'c:\data\TestDB2_log.ldf',

  REPLACE, NOUNLOAD,  STATS = 10, NORECOVERY

GO

-- see files offline

select * from sys.master_files  where database_id = (select database_id from sys.databases where name = 'testdb2')

--restore  diff and bring online

RESTORE DATABASE [TestDB2]

read_write_filegroups

  FROM  DISK = N'c:\data\TestDB_DIFF.bak'  WITH  FILE = 1, 

      MOVE N'TestDB_Part1' TO N'c:\data\TestDB2_Part1.mdf', 

      MOVE N'TestDB_Part2' TO N'c:\data\TestDB2_Part2.mdf', 

      MOVE N'TestDB_Part3' TO N'c:\data\TestDB2_Part3.mdf', 

      MOVE N'TestDB_Part4' TO N'c:\data\TestDB2_Part4.mdf', 

      MOVE N'TestDB_Part5' TO N'c:\data\TestDB2_Part5.mdf', 

      MOVE N'TestDB_log' TO N'c:\data\TestDB2_log.ldf',

  REPLACE, NOUNLOAD,  STATS = 10, recovery

GO

--hmmm got some errors

----The file "TestDB_Part2" was not fully restored by a database or file restore. The entire file must be successfully restored before applying this backup set.

----This RESTORE statement successfully performed some actions, but the database could not be brought online because one or more RESTORE steps are needed. Previous messages indicate reasons why recovery cannot occur at this point.

-- see files offline

select * from sys.master_files  where database_id = (select database_id from sys.databases where name = 'testdb2')

-- restore RO

use master

RESTORE DATABASE [TestDB2]  FILEGROUP = 'TestDB_Part2'  FROM

DISK = N'c:\data\TestDB_RO_Part2.bak'   WITH 

MOVE 'TestDB_Part2' TO N'c:\data\TestDB2_Part2.mdf'

-- All files online now...

select * from sys.master_files  where database_id = (select database_id from sys.databases where name = 'testdb2')

--restore the RO first (before the diff)

--restore RW portion w/ no recovery

use master

exec master.dbo.usp_killconnections [TestDB2]

drop database testdb2

RESTORE DATABASE [TestDB2]

read_write_filegroups

  FROM  DISK = N'c:\data\TestDB_RW.bak'  WITH  FILE = 1, 

      MOVE N'TestDB_Part1' TO N'c:\data\TestDB2_Part1.mdf', 

      MOVE N'TestDB_Part2' TO N'c:\data\TestDB2_Part2.mdf', 

      MOVE N'TestDB_Part3' TO N'c:\data\TestDB2_Part3.mdf', 

      MOVE N'TestDB_Part4' TO N'c:\data\TestDB2_Part4.mdf', 

      MOVE N'TestDB_Part5' TO N'c:\data\TestDB2_Part5.mdf', 

      MOVE N'TestDB_log' TO N'c:\data\TestDB2_log.ldf',

  REPLACE, NOUNLOAD,  STATS = 10, NORECOVERY

GO

-- see files offline

select * from sys.master_files  where database_id = (select database_id from sys.databases where name = 'testdb2')

-- restore RO

use master

RESTORE DATABASE [TestDB2]  FILEGROUP = 'TestDB_Part2'  FROM

DISK = N'c:\data\TestDB_RO_Part2.bak'   WITH 

MOVE 'TestDB_Part2' TO N'c:\data\TestDB2_Part2.mdf'

--same roll forward error:

----The roll forward start point is now at log sequence number (LSN) 20000000057700001. Additional roll forward past LSN 20000000061400001 is required to complete the restore sequence.

----This RESTORE statement successfully performed some actions, but the database could not be brought online because one or more RESTORE steps are needed. Previous messages indicate reasons why recovery cannot occur at this point.

-- see files in restoring

select * from sys.master_files  where database_id = (select database_id from sys.databases where name = 'testdb2')

--restore  diff and bring online

RESTORE DATABASE [TestDB2]

read_write_filegroups

  FROM  DISK = N'c:\data\TestDB_DIFF.bak'  WITH  FILE = 1, 

      MOVE N'TestDB_Part1' TO N'c:\data\TestDB2_Part1.mdf', 

      MOVE N'TestDB_Part2' TO N'c:\data\TestDB2_Part2.mdf', 

      MOVE N'TestDB_Part3' TO N'c:\data\TestDB2_Part3.mdf', 

      MOVE N'TestDB_Part4' TO N'c:\data\TestDB2_Part4.mdf', 

      MOVE N'TestDB_Part5' TO N'c:\data\TestDB2_Part5.mdf', 

      MOVE N'TestDB_log' TO N'c:\data\TestDB2_log.ldf',

  REPLACE, NOUNLOAD,  STATS = 10, recovery

GO

-- see all files online

select * from sys.master_files  where database_id = (select database_id from sys.databases where name = 'testdb2')

--ok, doesn't seem to matter much as long as we do all of them.  Lets make a new file readonly

-- set FG 3 to read only

ALTER DATABASE [TestDB2] MODIFY FILEGROUP [TestDB_Part3] READONLY

GO

-- Backup rw and new ro separately

BACKUP DATABASE [TestDB2]

READ_WRITE_FILEGROUPS

TO  DISK = N'c:\data\TestDB2_RW.bak'

WITH NOFORMAT, INIT, 

SKIP, NOREWIND, NOUNLOAD,  STATS = 10

GO

BACKUP DATABASE [TestDB2]

FILEGROUP = N'TestDB_Part3'

TO  DISK = N'c:\data\TestDB2_RO_Part3.bak'

WITH NOFORMAT, INIT, 

SKIP, NOREWIND, NOUNLOAD,  STATS = 10

GO

-- add data

use testdb2

INSERT INTO TestTable (ID, Date) -- Inserted in Partition 3 SHOULD FAIL due to read only

VALUES (25,GETDATE());

INSERT INTO TestTable (ID, Date) -- Inserted in Partition 5

VALUES (35,GETDATE());

INSERT INTO TestTable (ID, Date) -- Inserted in Partition 5

VALUES (55,GETDATE());

-- Backup DIFF

BACKUP DATABASE [TestDB2]

TO  DISK = N'c:\data\TestDB2_DIFF.bak'

WITH NOFORMAT, INIT, DIFFERENTIAL  ,

SKIP, NOREWIND, NOUNLOAD,  STATS = 10

GO

--backup Partial (RW) diff

BACKUP DATABASE [TestDB2]

READ_WRITE_FILEGROUPS

TO  DISK = N'c:\data\TestDB2_DIFFPartial.bak'

WITH NOFORMAT, INIT, DIFFERENTIAL  ,

SKIP, NOREWIND, NOUNLOAD,  STATS = 10

GO

-- list backup files.  We did not backup the read only file group in either diff

RESTORE FILELISTONLY from disk =  N'c:\data\TestDB2_Diff.bak' --has both read only files

RESTORE FILELISTONLY from disk =  N'c:\data\TestDB2_DiffPartial.bak' --has neither read only file

--try restoring with no read only files (just main and big diff)

use master

exec master.dbo.usp_killconnections [TestDB2]

drop database testdb2

RESTORE DATABASE [TestDB2]

read_write_filegroups

  FROM  DISK = N'c:\data\TestDB2_RW.bak'  WITH  FILE = 1, 

      MOVE N'TestDB_Part1' TO N'c:\data\TestDB2_Part1.mdf', 

      MOVE N'TestDB_Part2' TO N'c:\data\TestDB2_Part2.mdf', 

      MOVE N'TestDB_Part3' TO N'c:\data\TestDB2_Part3.mdf', 

      MOVE N'TestDB_Part4' TO N'c:\data\TestDB2_Part4.mdf', 

      MOVE N'TestDB_Part5' TO N'c:\data\TestDB2_Part5.mdf', 

      MOVE N'TestDB_log' TO N'c:\data\TestDB2_log.ldf',

  REPLACE, NOUNLOAD,  STATS = 10, NORECOVERY

GO

-- see all files offline

select * from sys.master_files  where database_id = (select database_id from sys.databases where name = 'testdb2')

--restore  diff and bring online

RESTORE DATABASE [TestDB2]

read_write_filegroups

  FROM  DISK = N'c:\data\TestDB2_DIFF.bak'  WITH  FILE = 1, 

      MOVE N'TestDB_Part1' TO N'c:\data\TestDB2_Part1.mdf', 

      MOVE N'TestDB_Part2' TO N'c:\data\TestDB2_Part2.mdf', 

      MOVE N'TestDB_Part3' TO N'c:\data\TestDB2_Part3.mdf', 

      MOVE N'TestDB_Part4' TO N'c:\data\TestDB2_Part4.mdf', 

      MOVE N'TestDB_Part5' TO N'c:\data\TestDB2_Part5.mdf', 

      MOVE N'TestDB_log' TO N'c:\data\TestDB2_log.ldf',

  REPLACE, NOUNLOAD,  STATS = 10, recovery

GO

--despite saying the file is there in the backup, we still get issues with RO files

----The file "TestDB_Part2" was not fully restored by a database or file restore. The entire file must be successfully restored before applying this backup set.

----The file "TestDB_Part3" was not fully restored by a database or file restore. The entire file must be successfully restored before applying this backup set.

-- see all files offline

select * from sys.master_files  where database_id = (select database_id from sys.databases where name = 'testdb2')

-- restore RO

use master

RESTORE DATABASE [TestDB2]  FILEGROUP = 'TestDB_Part2'  FROM

DISK = N'c:\data\TestDB_RO_Part2.bak'   WITH 

MOVE 'TestDB_Part2' TO N'c:\data\TestDB2_Part2.mdf'

--warning we are still missing 3 so cannot come online

use master

RESTORE DATABASE [TestDB2]  FILEGROUP = 'TestDB_Part3'  FROM

DISK = N'c:\data\TestDB2_RO_Part3.bak'   WITH 

MOVE 'TestDB_Part2' TO N'c:\data\TestDB2_Part2.mdf'

-- see all files offline

select * from sys.master_files  where database_id = (select database_id from sys.databases where name = 'testdb2')

--try restoring with partial diff

use master

exec master.dbo.usp_killconnections [TestDB2]

drop database testdb2

RESTORE DATABASE [TestDB2]

read_write_filegroups

  FROM  DISK = N'c:\data\TestDB2_RW.bak'  WITH  FILE = 1, 

      MOVE N'TestDB_Part1' TO N'c:\data\TestDB2_Part1.mdf', 

      MOVE N'TestDB_Part2' TO N'c:\data\TestDB2_Part2.mdf', 

      MOVE N'TestDB_Part3' TO N'c:\data\TestDB2_Part3.mdf', 

      MOVE N'TestDB_Part4' TO N'c:\data\TestDB2_Part4.mdf', 

      MOVE N'TestDB_Part5' TO N'c:\data\TestDB2_Part5.mdf', 

      MOVE N'TestDB_log' TO N'c:\data\TestDB2_log.ldf',

  REPLACE, NOUNLOAD,  STATS = 10, NORECOVERY

GO

-- see all files offline

select * from sys.master_files  where database_id = (select database_id from sys.databases where name = 'testdb2')

--restore  diff and bring online

RESTORE DATABASE [TestDB2]

read_write_filegroups

  FROM  DISK = N'c:\data\TestDB2_DIFFPartial.bak'  WITH  FILE = 1, 

      MOVE N'TestDB_Part1' TO N'c:\data\TestDB2_Part1.mdf', 

      MOVE N'TestDB_Part2' TO N'c:\data\TestDB2_Part2.mdf', 

      MOVE N'TestDB_Part3' TO N'c:\data\TestDB2_Part3.mdf', 

      MOVE N'TestDB_Part4' TO N'c:\data\TestDB2_Part4.mdf', 

      MOVE N'TestDB_Part5' TO N'c:\data\TestDB2_Part5.mdf', 

      MOVE N'TestDB_log' TO N'c:\data\TestDB2_log.ldf',

  REPLACE, NOUNLOAD,  STATS = 10, recovery

GO

--same issues with RO files

----The file "TestDB_Part2" was not fully restored by a database or file restore. The entire file must be successfully restored before applying this backup set.

----The file "TestDB_Part3" was not fully restored by a database or file restore. The entire file must be successfully restored before applying this backup set.

-- see all files offline

select * from sys.master_files  where database_id = (select database_id from sys.databases where name = 'testdb2')

-- restore RO

use master

RESTORE DATABASE [TestDB2]  FILEGROUP = 'TestDB_Part2'  FROM

DISK = N'c:\data\TestDB_RO_Part2.bak'   WITH 

MOVE 'TestDB_Part2' TO N'c:\data\TestDB2_Part2.mdf'

--warning we are still missing 3 so cannot come online

use master

RESTORE DATABASE [TestDB2]  FILEGROUP = 'TestDB_Part3'  FROM

DISK = N'c:\data\TestDB2_RO_Part3.bak'   WITH 

MOVE 'TestDB_Part2' TO N'c:\data\TestDB2_Part2.mdf'

-- see all files online

select * from sys.master_files  where database_id = (select database_id from sys.databases where name = 'testdb2')

--it seems we cannot come online until all full, diffs and RO files are restored.  This is different

--  than when we have just a partial backup and RO (no diffs), where we can come online after just the partial backup

--one more scenario.  what if i make something read only and then take a diff backup before a 'full' partial (i should really look up the correct terminology)

-- set FG 4 to read only

ALTER DATABASE [TestDB2] MODIFY FILEGROUP [TestDB_Part4] READONLY

GO

-- add data

use testdb2

INSERT INTO TestTable (ID, Date) -- Inserted in Partition 4, SHOULD FAIL due to read only

VALUES (35,GETDATE());

INSERT INTO TestTable (ID, Date) -- Inserted in Partition 5

VALUES (55,GETDATE());

--backup RO

BACKUP DATABASE [TestDB2]

FILEGROUP = N'TestDB_Part4'

TO  DISK = N'c:\data\TestDB2_RO_Part4.bak'

WITH NOFORMAT, INIT, 

SKIP, NOREWIND, NOUNLOAD,  STATS = 10

GO

-- Backup DIFF

BACKUP DATABASE [TestDB2]

TO  DISK = N'c:\data\TestDB2_DIFF2.bak'

WITH NOFORMAT, INIT, DIFFERENTIAL  ,

SKIP, NOREWIND, NOUNLOAD,  STATS = 10

GO

--backup Partial (RW) diff

BACKUP DATABASE [TestDB2]

READ_WRITE_FILEGROUPS

TO  DISK = N'c:\data\TestDB2_DIFFPartial2.bak'

WITH NOFORMAT, INIT, DIFFERENTIAL  ,

SKIP, NOREWIND, NOUNLOAD,  STATS = 10

GO

-- list backup files.  We did not backup the read only file group in either diff

RESTORE FILELISTONLY from disk =  N'c:\data\TestDB2_Diff2.bak' --has all read only files

RESTORE FILELISTONLY from disk =  N'c:\data\TestDB2_DiffPartial2.bak' --has none of the read only files

--restore test

use master

exec master.dbo.usp_killconnections [TestDB2]

drop database testdb2

RESTORE DATABASE [TestDB2]

read_write_filegroups

  FROM  DISK = N'c:\data\TestDB2_RW.bak'  WITH  FILE = 1, 

      MOVE N'TestDB_Part1' TO N'c:\data\TestDB2_Part1.mdf', 

      MOVE N'TestDB_Part2' TO N'c:\data\TestDB2_Part2.mdf', 

      MOVE N'TestDB_Part3' TO N'c:\data\TestDB2_Part3.mdf', 

      MOVE N'TestDB_Part4' TO N'c:\data\TestDB2_Part4.mdf', 

      MOVE N'TestDB_Part5' TO N'c:\data\TestDB2_Part5.mdf', 

      MOVE N'TestDB_log' TO N'c:\data\TestDB2_log.ldf',

  REPLACE, NOUNLOAD,  STATS = 10, NORECOVERY

GO

-- see all files offline

select * from sys.master_files  where database_id = (select database_id from sys.databases where name = 'testdb2')

--restore  diff and bring online

RESTORE DATABASE [TestDB2]

read_write_filegroups

  FROM  DISK = N'c:\data\TestDB2_DIFF2.bak'  WITH  FILE = 1, 

      MOVE N'TestDB_Part1' TO N'c:\data\TestDB2_Part1.mdf', 

      MOVE N'TestDB_Part2' TO N'c:\data\TestDB2_Part2.mdf', 

      MOVE N'TestDB_Part3' TO N'c:\data\TestDB2_Part3.mdf', 

      MOVE N'TestDB_Part4' TO N'c:\data\TestDB2_Part4.mdf', 

      MOVE N'TestDB_Part5' TO N'c:\data\TestDB2_Part5.mdf', 

      MOVE N'TestDB_log' TO N'c:\data\TestDB2_log.ldf',

  REPLACE, NOUNLOAD,  STATS = 10, recovery

GO

--same issues with RO files

----The file "TestDB_Part2" was not fully restored by a database or file restore. The entire file must be successfully restored before applying this backup set.

----The file "TestDB_Part3" was not fully restored by a database or file restore. The entire file must be successfully restored before applying this backup set.

-- see all files offline

select * from sys.master_files  where database_id = (select database_id from sys.databases where name = 'testdb2')

-- restore RO

use master

RESTORE DATABASE [TestDB2]  FILEGROUP = 'TestDB_Part2'  FROM

DISK = N'c:\data\TestDB_RO_Part2.bak'   WITH 

MOVE 'TestDB_Part2' TO N'c:\data\TestDB2_Part2.mdf'

use master

RESTORE DATABASE [TestDB2]  FILEGROUP = 'TestDB_Part3'  FROM

DISK = N'c:\data\TestDB2_RO_Part3.bak'   WITH 

MOVE 'TestDB_Part3' TO N'c:\data\TestDB2_Part3.mdf'

use master

RESTORE DATABASE [TestDB2]  FILEGROUP = 'TestDB_Part4'  FROM

DISK = N'c:\data\TestDB2_RO_Part4.bak'   WITH 

MOVE 'TestDB_Part4' TO N'c:\data\TestDB2_Part4.mdf'

-- see all files online

select * from sys.master_files  where database_id = (select database_id from sys.databases where name = 'testdb2')

--so this all works about how we would expect, except that you cannot come online after just a base backup and

-- a partial diff restore.  (you must get your RO online as well before the db will come up).  note that if you are

-- restoring in place and your Read only hasn't changed since your base backup,  your RO will be found as in:

--restore with no drop

RESTORE DATABASE [TestDB2]

read_write_filegroups

  FROM  DISK = N'c:\data\TestDB2_RW.bak'  WITH  FILE = 1, 

      MOVE N'TestDB_Part1' TO N'c:\data\TestDB2_Part1.mdf', 

      MOVE N'TestDB_Part2' TO N'c:\data\TestDB2_Part2.mdf', 

      MOVE N'TestDB_Part3' TO N'c:\data\TestDB2_Part3.mdf', 

      MOVE N'TestDB_Part4' TO N'c:\data\TestDB2_Part4.mdf', 

      MOVE N'TestDB_Part5' TO N'c:\data\TestDB2_Part5.mdf', 

      MOVE N'TestDB_log' TO N'c:\data\TestDB2_log.ldf',

  REPLACE, NOUNLOAD,  STATS = 10, NORECOVERY

GO

-- see all files offline

select * from sys.master_files  where database_id = (select database_id from sys.databases where name = 'testdb2')

--restore  diff and bring online

RESTORE DATABASE [TestDB2]

read_write_filegroups

  FROM  DISK = N'c:\data\TestDB2_DIFF.bak'  WITH  FILE = 1, 

      MOVE N'TestDB_Part1' TO N'c:\data\TestDB2_Part1.mdf', 

      MOVE N'TestDB_Part2' TO N'c:\data\TestDB2_Part2.mdf', 

      MOVE N'TestDB_Part3' TO N'c:\data\TestDB2_Part3.mdf', 

      MOVE N'TestDB_Part4' TO N'c:\data\TestDB2_Part4.mdf', 

      MOVE N'TestDB_Part5' TO N'c:\data\TestDB2_Part5.mdf', 

      MOVE N'TestDB_log' TO N'c:\data\TestDB2_log.ldf',

  REPLACE, NOUNLOAD,  STATS = 10, recovery

GO

-- see all files online

select * from sys.master_files  where database_id = (select database_id from sys.databases where name = 'testdb2')